Splitting a String

  • Thread starter Thread starter AMK4
  • Start date Start date
A

AMK4

I need to split a string into two parts: the first word, and whatever's
left of the remaining string. I do this to capture the first word:
Code:
 
if the original string is in A1
the formula you show in A2
in A3 as an example

=Right(A1,len(A1)-(len(A2)+1))

or if you want to us find to find it again

=Right(A1,len(A1)-Find(" ",A1))

or you can use the mid function

=MID(A1,FIND(" ",A1)+1,LEN(A1))
 
There are a couple of ways to do this.

lastWord = right(longString, len(longString) - Find(" ", longString) + 1)

or

lastWord = mid(longString, Find(" ", longString) + 1, 256)
 
Here is a short one:

rest = Mid(longString,1+len(firstWord ))

then use TRIM if you want to get rid of noise spaces.

Regards,
Antonio Elinon
 
I need to split a string into two parts: the first word, and whatever's
left of the remaining string. I do this to capture the first word:
Code:
Click to expand...

And another method:

With your formula above in B1:

=SUBSTITUTE(longString,B1&" ","")


--ron
 
This will not work as you could end up removing similar occurences of the
firstWord in the remaining portion, eg. "aa bbb aa xxx" will have an
incorrect remainder of "bbb xxx".

Regards,
Antonio Elinon
 
This will not work as you could end up removing similar occurences of the
firstWord in the remaining portion, eg. "aa bbb aa xxx" will have an
incorrect remainder of "bbb xxx".

Regards,
Antonio Elinon
Click to expand...

Thanks for pointing that out.
--ron
 
This will not work as you could end up removing similar occurences of the
firstWord in the remaining portion, eg. "aa bbb aa xxx" will have an
incorrect remainder of "bbb xxx".

Regards,
Antonio Elinon
Click to expand...


Thanks for pointing that out.

The proper formula, using the SUBSTITUTE method, should be:

=SUBSTITUTE(longString,B1&" ","",1)


--ron
 
Back
Top