Delimiting with a formula

[finwiz]

Hi hope you're are doing better that I am. My issue is breaking a text string that is separated with periods ex. I.Want.To.Separate.This.String

I need the original string and I have separated the first two words using the following formulas

1st column
=IFERROR((LEFT(A3,(SEARCH(".",A3))-1)), A3)
This would be "I"

2nd Column
=IFERROR((TRIM(LEFT(MID(A3,FIND(".",A3)+1,255),FIND(".",MID(A3,FIND(".",A3)+1,255))-1))),A3)
This would be "Want"

but after that all my formulas return a insufficient value like "To.Separate" for the third column. I also only want to go to four columns in this exercise, so an "I", "Want", "To", "Separate" individual columns. how do I finish this off?

My third column is =IFERROR((TRIM((MID(MID(A5,FIND(".",A5)+1,255),FIND(".",MID(A5,FIND(".",A5)+1,255))+1,FIND(".",MID(A5,FIND(".",A5)+1,255)))))),A5)

If that helps any.

Thanks guys you do awesome work!

[Pepe Le Mokko]

Could you not use Data - Text to columns and use the colon as delimiter?

[finwiz]

Not sure if my message went through.. so just testing

---------- Post added at 09:45 AM ---------- Previous post was at 09:43 AM ----------

So the reason why I'm not is due to the manual labor of the text-to-columns. I want this sheet to aggregate about 15 others possibly multiple times a day. Which turns out to be a lot of text-to-columns. This is the best way to keep the original data intact and be able to manipulate it without much effort.

[oeldere]

mabye you can post an example of your workbook.

[finwiz]

Sorry not sure what you need? Do you need a better explanation of the original post, or the use of my formulas in it?

Sorry just trying to figure out what you need. Also I can't attach my document as it may have some sensitive data.

Thanks

[oeldere]

an excel file without confidentional information.

[finwiz]

I hope this helps. I was hoping to get up to five or six columns.

Thanks

[oeldere]

this is the result, when i use

excel 2007

data => text to column

[finwiz]

Could you please look at the second tab? Using text to columns is easy if I want to do it once. I need to do this a minimum once a day with about 15 worksheets.

If you look at the second tab on my example.xlxs it will show you the formulas I have used for the first two and the third being corrupted some how.

Thanks

[oeldere]

I that case i don't know what you are try to achieve.

[finwiz]

I have about 15 workbooks that I aggregate data from. So "sheet 1" would be linked to these documents. as they are updated the file will automatically update. Meaning text-to-columns is useless. I would have to do text to columns over again. Also the naming conventions can have up to 12 periods (.), but I only want the first few. That is why I named this thread delimiting with a formula.

Thanks

---------- Post added at 02:21 PM ---------- Previous post was at 02:20 PM ----------

If you look I have done it with the first 2, but after that I have a weird issue... Anyone know what I've done wrong and how I can continue a few other columns?

[Søren Larsen]

The follwoing should be generic for all instances of strings; place it in B2 and copy down and across:

Please Login or Register  to view this content.

[finwiz]

Wow! Soren you are amazing! This is exactly what I needed!

Thank you

[Søren Larsen]

You're welcome! If all's sorted, then don't forget to mark your thread as solved...