Manipulating strings (finding the substring between two spaces)

[kaptenstofil]

Hi guys,

I was unsure where to post this as this is maybe easier solved with a combination of formulas but anyway, here goes. I have a couple of strings like this in my worksheet:

AB 179 EFGH
AB 42 EFGH
AB 44798 EFGH
...and so forth

I'd very much like to pull the substring that is between the two spaces (i.e. 179, 42 and 44798 respectively). I've done a whole lot of experimenting and usually end up (very mad) with something like "AB 179" or "EFGH". In addition, I'm having trouble getting it to work for all different lengths of the substring (i.e. 3, 2, 5 and n...)

Any help on this matter would be greatly appreciated.

Many thanks and godspeed!

[Marc L]

Hi,

just use Split function, see in VBA inner help …

[Pete_UK]

If those strings are in column A, starting with A1, then you can use this formula (e.g. in B1):

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))

then copy down. Note that these will still be strings - if you want to convert them to actual numbers, just add +0 to the end of the formula.

Hope this helps.

Pete

[kaptenstofil]

Hi,

just use Split function, see in VBA inner help …

Wow! That was almost too easy. I ended up with something like this:

Please Login or Register  to view this content.

Adding the (1) at the end was a simple way of returning only the second fragment of the string!

If those strings are in column A, starting with A1, then you can use this formula (e.g. in B1):

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))

then copy down. Note that these will still be strings - if you want to convert them to actual numbers, just add +0 to the end of the formula.

Hope this helps.

Pete

Much obliged! I am sure this will prove helpful down the line...I was quite unaware of the =FIND-function.

Thanks to both of you!