If you find the VBA and Regex a little intimidating there are the Replace and Match activities in your activities panel which help to guide you a little more.
Here I have first split the input string by newline into an array and then grabbed the third line to use as my input. By then using the Replace Activity you can identify both the house number and street name to output to individual variables.
Main.xaml (6.8 KB)
I have a String like this
“Enschede
7514 CB
H.B. Blijdensteinlaan 18
Netherlands”
The only things I need in this string are: H.B. Blijdensteinlaan 18 so this is line 3 (always line 3) now i want to put everything till the number in one variable and the number in one variable.
So i need this:
Variable 1: Streetname: “H.B. Blijdensteinlaan”
Variable 2: Housenumber: “18”
Hi @438654
Would this fit your requirement?
It is some very basic string manipulation. The assumption is that you start with 1 String variable with this text:
Enschede
7514 CB
H.B. Blijdensteinlaan 18
Netherlands
For this example, I loaded it from a text file, but you can just as easy assign it the value from your row.
And you end up with two new String variables, one for the Street name:
H.B. Blijdensteinlaan
And one for the number:
18
The Regex can also be a bit improved for the street number, depending on your situation. As it is: \d+, it will not detect house numbers that contain a letter, i.e. 18a. In this case, it would only detect the digits and skip the ‘a’.
See attached example:
ExtractStreenName.zip (2.0 KB)
In the process, I first split the string into multiple lines based on a new line character, which allows me to focus on the 3rd row every time. Then, I run some basic Regex to assign street name and street number to separate variables.
I think it’s a little different. My String is read from Excel so it’s an Generic Value. When i put this in a String and then try to split like this: adres.Split({System.Environment.NewLine}, System.StringSplitOptions.RemoveEmptyEntries)(2) I
Its telling me that the index was outside the bounds of the array.
Your solution is good! The only thing is that Your inputstring is assigned by yourself. My assignedstring is an GenericValue from Excel. When i implement your solution it tells me that the index was outside the bounds of the array. Its telling me that with this adres.Split({System.Environment.NewLine}, System.StringSplitOptions.RemoveEmptyEntries)(2)
I don’t think the Generic type has much to do with the error you are getting. Try something like this, and the error will probably stay the same:
adres.ToString.Split({System.Environment.NewLine}, System.StringSplitOptions.RemoveEmptyEntries)(2)
In fact, I’ve just tested this above scenario after converting my input variable to a Generic type variable and it simply worked as it should.
Please debug your process a bit. To do so, add a Write Line activity with your Generic variable and post a screenshot from your Output pane. My bet is that your input variable is a bit shorter and the Split method does not return an element with an index of 2 after its called (thus the error you are getting).
I would try checking other indexes in your Split method, try these first:
adres.ToString.Split({System.Environment.NewLine}, system.StringSplitOptions.RemoveEmptyEntries)(0)
adres.ToString.Split({System.Environment.NewLine}, system.StringSplitOptions.RemoveEmptyEntries)(1)
I already did and they al get the same error
Can you try
and see what outputs you’re getting with these?
None of them works
What specific errors are you getting?
Errors are there with assigning
Sorry @DanielMitchell the first one works and gives me Enschede. The other gives error like cannot assign from type System.int32 to type System.string
The second two are Integers, you can’t assign them to a String. That’s what that error message means.
You could log them to see the difference in array length between splitting and splitting while removing empty entries.
If adres.ToString.Split({System.Environment.NewLine})(0) gives you “Enschede” then adres.ToString.Split({System.Environment.NewLine})(2) should give you the line you need. Then you can use the regex that @ronanpeter and @loginerror provided above to separate the pieces you need.
Yeah i know but the problem is when i change the (0) to (2) so i should get the line i need. It gives me an error index was outside the bounds of the array
What is the value if you log
adres.ToString.Split({System.Environment.NewLine}).Count.ToString
Sorry worktime was over but the value of that is 1 so thats the problem…
When i use: adres.ToString.Split({System.Environment.NewLine})(0)
it gives me the an compiling error. It only works when i remove the .ToString
What is the output in the console when you use index of 1?
