I have an Excel file that looks something like this
In the Address column in row 49 i want:
Column B, C and D concatenated and they have to follow this syntax:
Streetname number letter.
Sometimes the number has less than 3 digits but i always want them to have 3 so for example in row 40 in file 1 there’s only 2 i want the output to then be 017.
And for row 42 i want it to output me Streetname 022 C
I used three examples from row 48-51 to showcase what i mean.
For row H and I i just want the data copied straight over if the field is not blank.
I hope it makes sense, please ask any questions.
So far I’ve made some good progress, I filter all the correct columns in Excel and i concatenate the streetname, number and letter. I also make sure that the streetnumber persist of 3 digits by using the method: PadLeft(3, "0"c).
Now I’m currently stuck at what to do with the streetnumbers that also contain a letter. I somehow need to split it and move it to the letter section.
For splitting you can try this
System.Text.RegularExpressions.Regex.Match(str,"\d+").value - this will give only number from the given str - where str is the input variable that we provide
Similarly or letters
It would return empty string if not found any letter or number
Hope this helps
Very good, i can see that indeed manipulates what i’m trying to do now i just need it to match my usecase exactly. Since i want to save the letter and not just delete it from my row.
When using [A-Z] it ruins all the streetnumbers (obviously)
When using “\d+” it just removes the letter, which is correct. BUT i need to use the letter to be added after the streetnumber. If that makes sense?
These are to be used in the row output and then the output of these activities can be used to concatenate in the street address…dont use these functions on street address directly
If you still are facing issue…please show how you are using from there we can modify
I found a way by using row.Item(1).ToString.Replace(system.text.RegularExpressions.Regex.Match(row(1).ToString,“\d+”).Value,“”).Trim and then assigning it to a temporary row so I could get it later.
Thanks a lot
Happy Automation. Glad it is resolved
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.