Extract data from a string - Varying length, between spaces

**daddylonglegs** · 09-07-2009, 08:01 PM

The formula should work assuming you have a single space between each piece of text. If there might be additional spaces try adding another TRIM function to Donkeyote's suggestion, i.e.

=TRIM(MID(SUBSTITUTE(TRIM($A1)," ",REPT(" ",100)),1+(COLUMNS($B1:B1)-1)*100,100))

**Lee.shop** · 09-07-2009, 08:19 PM

It's not happy about the brackets now...

I'm off to bed before I do something silly and delete the lot.... LOL!

L.

**Lee.shop** · 09-10-2009, 10:25 AM

SORTED!!!!

Cheers for all your help guys, I had another look at this using a PC and Excel 2003 - The first formula worked as soon as I opened the file, I then copied it over to the PPC and it's looks to be working on there too now!

Excellent forum!!!! L.

**Xalaal2009** · 10-01-2009, 02:50 PM

Originally Posted by daddylonglegs

The formula should work assuming you have a single space between each piece of text. If there might be additional spaces try adding another TRIM function to Donkeyote's suggestion, i.e.

=TRIM(MID(SUBSTITUTE(TRIM($A1)," ",REPT(" ",100)),1+(COLUMNS($B1:B1)-1)*100,100))

Daddylonglegs:
Every solution can be shared/used by multiple users. I was looking for exactly same (with slight difference) code & it solved my problems without posting!