Splitting a String

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:

firstWord = Left(longString, Find(" ", longString) - 1)

... however I don't know what's the easiest (smartest?) way to figure out the remaining part of the string.

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))


--
Regards,
Tom Ogilvy



"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> 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:
> --------------------
> firstWord = Left(longString, Find(" ", longString) - 1)
> --------------------
> ... however I don't know what's the easiest (smartest?) way to figure
> out the remaining part of the string.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=511600
>

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)

--
HTH...

Jim Thomlinson


"AMK4" wrote:

>
> 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:
> --------------------
> firstWord = Left(longString, Find(" ", longString) - 1)
> --------------------
> ... however I don't know what's the easiest (smartest?) way to figure
> out the remaining part of the string.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=511600
>
>

maybe this

=RIGHT(longString,(LEN(longString)-FIND(" ",longString,1)))

--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> 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:
> --------------------
> firstWord = Left(longString, Find(" ", longString) - 1)
> --------------------
> ... however I don't know what's the easiest (smartest?) way to figure
> out the remaining part of the string.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=511600
>

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

"AMK4" wrote:

>
> 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:
> --------------------
> firstWord = Left(longString, Find(" ", longString) - 1)
> --------------------
> ... however I don't know what's the easiest (smartest?) way to figure
> out the remaining part of the string.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=511600
>
>

On Sun, 12 Feb 2006 22:12:43 -0600, AMK4
<[email protected]> wrote:

>
>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:
>--------------------
> firstWord = Left(longString, Find(" ", longString) - 1)
>--------------------
> ... however I don't know what's the easiest (smartest?) way to figure
>out the remaining part of the string.

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

"Ron Rosenfeld" wrote:

> On Sun, 12 Feb 2006 22:12:43 -0600, AMK4
> <[email protected]> wrote:
>
> >
> >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:
> >--------------------
> > firstWord = Left(longString, Find(" ", longString) - 1)
> >--------------------
> > ... however I don't know what's the easiest (smartest?) way to figure
> >out the remaining part of the string.
>
> And another method:
>
> With your formula above in B1:
>
> =SUBSTITUTE(longString,B1&" ","")
>
>
> --ron
>

On Mon, 13 Feb 2006 14:19:27 -0800, "Antonio Elinon"
<[email protected]> wrote:

>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

Thanks for pointing that out.
--ron

On Mon, 13 Feb 2006 14:19:27 -0800, "Antonio Elinon"
<[email protected]> wrote:

>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


Thanks for pointing that out.

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

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


--ron