PDA

View Full Version : Extracting data from a variable length string



austenr
04-25-2018, 10:31 AM
Suppose you have the following:

smith - xx - john
anderson - xx - april
williams - xx - john

What i want to do is to have it end up like

xx john smith
xx april anderson
xx john williams

You cant use LEFT or MID or RIGHT afaik because of the length of the string.

Anyone have any suggestions on how to accomplish this?

The string would be in A1, A2, etc.

austenr
04-25-2018, 10:51 AM
ok so i got this far:

anderson - xx - april

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

i can get Anderson

how do you get April in the next adjoining cell?

SamT
04-25-2018, 01:59 PM
Function Recombine(strIn As String)AS String
'Assumes strIn is a 5 part string like "smith - xx - john"
'Returns a 3 part string like "xx john smith"

Dim Tmp
tmp = Split(strIn, "-")
Recombine = Trim(Tmp(1) & tmp(2) & tmp(0))
End Function

p45cal
04-25-2018, 03:03 PM
try:
=MID(A1,FIND(" - ",A1)+3,FIND(" - ",A1,FIND(" - ",A1)+1)-FIND(" - ",A1)-3)
to get the xx

=MID(A1,FIND(" - ",A1,FIND(" - ",A1)+1)+3,999)
to get the john

=LEFT(A1, FIND(" - ",A1)-1 )
to get the smith.

austenr
04-25-2018, 06:00 PM
try:
=MID(A1,FIND(" - ",A1)+3,FIND(" - ",A1,FIND(" - ",A1)+1)-FIND(" - ",A1)-3)
to get the xx

=MID(A1,FIND(" - ",A1,FIND(" - ",A1)+1)+3,999)
to get the john

=LEFT(A1, FIND(" - ",A1)-1 )
to get the smith.


Thanks guys. Both work well.