Pulling sections of a string

**weeble33** · 08-07-2012, 10:35 AM

Hello,

I have a column of strings that have the format XX.XXX.XXX.XXXXX.XXXX Really each section varies in length, but there are 5 sections, each separated by a period. And some sections will contain no information: XX...XXXXXXX.XX I would like to be able to separate this data into 5 columns. I know it'll probably consist of Mid() and Len(), but I don't know how to tell it to pull everything between the 3rd and 4th period for example.

Thank you

**Kyle123** · 08-07-2012, 10:44 AM

Have you tried text to columns?

**BenMiller** · 08-07-2012, 10:55 AM

With your text in A1, use this for the first one:

=LEFT(A1,FIND(".",A1)-1)

This for the 2nd 3rd and 4th (place in cell and drag across):

=MID($A$1,FIND("#",SUBSTITUTE($A$1,".","#",COLUMN(A1)))+1,FIND("#",SUBSTITUTE($A$1,".","#",COLUMN(B1)))-FIND("#",SUBSTITUTE($A$1,".","#",COLUMN(A1)))-1)

And this for the last:

=MID($A$1,FIND("#",SUBSTITUTE($A$1,".","#",COLUMN(D1)))+1,99)

**weeble33** · 08-07-2012, 10:52 AM

Kyle123,

The data to be separated is a function. Using the text to columns seems to want to separate the formula itself, not the result. Also, this data is dynamic. It changes constantly based on a Live connection to a database in Quickbooks. The column can grow or shrink basedd on the data. I don't know if text to columns functions like sort, in that it's good once, but if the data changes or is based on references, it's useless.

**weeble33** · 08-07-2012, 11:42 AM

Wonderful! Thank you kindly, BenMiller. The column() threw me for a bit of a loop (my formulas are being put way out in Columns AE-AI) I realized I shouldn't be changing the reference to Column A and B. A number would've worked just as well, but for dragging across to the other columns, I understand why.

Thanks again.

**BenMiller** · 08-07-2012, 11:47 AM

No problem

Glad to help ... Appreciate the feedback!

Yes, the COLUMNs are just giving you the numbers 2 and 3; and then 3 and 4, etc. for the SUBSTITUTE. Just changed the hard-coded numbers into COLUMN formulas, so they'll change as you drag it.

Glad it worked out!