Excel: Formula to extract a string of text delimited by markers from cells

Question

I'm messing with a spreadsheet containing postal addresses that have been inserted in the cells' comments Each comment contain an address composed of a variable number of lines (damn UK addresses, they can have up to 7 lines!) in the following format:

Line1,
Line2,
Line3,
[...],
State

With my poor skills, I've managed to extract the comment with a VBA script, obtaining the following string on a single cell:

Line1,Line2,Line3,[...],State

At this point each string between commas must be extracted to its own cell. I've managed to extract the 1st 3 lines with the following formulas:

For Line1:

=LEFT(A8;(SEARCH(",";A8))-1)

For Line2:

=MID(A8; SEARCH(",";A8)+1; SEARCH(","; A8; SEARCH(","; A8)+1)-SEARCH(",";A8)-1)

For Line3:

=MID(A8; SEARCH(",";A8;SEARCH(",";A8;SEARCH(",";A8;SEARCH(",";A8)))+1)+1;SEARCH(","; A8; SEARCH(","; A8;SEARCH(",";A8)+1)+1)-SEARCH(",";A8;SEARCH(",";A8)+1)-1)

From this point I start to get overflow errors from my brain... I probably need some days of sleep.

Can anybody help me to get to "line6", and finally suggest me how to pull out the "State line" which ends without comma?

I thought I could pull out the "State" line with =RIGHT(",";SEARCH(",";A8)-1) but I'm obviously doing something wrong because that pulls out a comma instead of a string.

I guess I could do everything with a VBA script, but I'm not that skilled yet :(

Solution 1

If you are wanting to do this programmatically instead of using a built-in, check out the split function for chopping up your comma separated string. It will split up your input string into an array. Then you can do whatever you like with the array.

 Dim Names() As String

 Names() = Split(inputValue, ",")
 For i = 0 To UBound(Names)
    ' do what you want with each piece
 Next

Gary's Student's answer is great for using the built-in functions.

Solution 2

With comma separated data in A1, in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across. For example:

Note:

Why not use TextToColumns ?

• The row of formulas re-calculates automatically if A1 changes.
• The row of formulas will work even if A1, itself, contains a formula.

Comments

[KevenDenen]

Interesting method for achieving this. How did you ever come up with this?

[KevenDenen]

Well, yes, he copy & pasted it from another answer he gave. I'd like to know where he got the idea from originally. It's an odd solution.

[Gary's Student]

@KevenDenen..............I did not develop this formula........if I can retrieve the "source" I will update my Answer.

[KevenDenen]

That is a fascinating formula for extracting data.