=RIGHT function

[Eva Shanley]

How do I use get the right-most characters in a cell that
contains an entry such as 12 12345 66, which needs to be
in three separate columns. The numbers are separated by
spaces, and the entries will have varying numbers of
characters. I have formulas to extract the 12 and 12345,
but am having trouble getting the 66. Thanks!

 

[Peo Sjoblom]

If it is always 2 spaces you can use

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",2))+1,255)

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[hgrove]

Eva Shanley wrote...

How do I use get the right-most characters in a cell that
contains an entry such as 12 12345 66, which needs to be in
three separate columns. The numbers are separated by spaces,
and the entries will have varying numbers of characters. I have
formulas to extract the 12 and 12345, but am having trouble
getting the 66. . . .

If there are only ever 3 space-separated groups of numerals, you coul
pull the third set using

=MID(TRIM(x),FIND(" ",TRIM(x),FIND(" ",TRIM(x))+1)+1,1024)

In general, MID is more useful than RIGHT for pulling characters at th
end of strings. RIGHT is only useful when it's easier to determine th
number of characters on the right side of the string than the startin
character position (from the left/beginning) of the rightmos
substring

 

[Eva Shanley]

I believe there will always be 2 spaces, and the formula
does exactly what I need. But tell me, how do you know
how to put that formula together? Does it take years of
practice, or just logic that I don't seem to have? I tried
all sorts of things.

 

[hgrove]

Peo Sjoblom wrote...

If it is always 2 spaces you can use

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",2))+1,255)

...

You must live in a blessed world in which there are always just th
right number of space characters. Then again I'm in it, so it can't b
all that blessed

 

[Peo Sjoblom]

Lets dissect it, if you look at how substitute works you can see that it is

SUBSTITUTE(cell(s),old_text,new_text,instance_number)

in the example gives

SUBSTITUTE(A1,"space","^^",2)


since you have 2 spaces the instance number will be 2, then I substitute the
second space with
a character that is not likely to be there, e.g. ^^, that means that after
the substitute is done it looks like

12 12345^^66

then I use FIND to find the position of "^^" which is 9 and since I don't
want to include the last space I add
1 to that number, then just parse everything that's to the right

By reading these ngs you will pick up a lot and can later applied the
techniques

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Arvi Laanemets]

Hi

Use TextToColumns instead of formula (Data.TextToColumns, delimited with
space as delimiter)

 

[Dave R.]

Here's another. As long as there are spaces as you indicate it will yank the
last "word".

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))

 

[Eva Shanley]

Thanks to all for your great formulas, and a special
thanks for Peo's explanation!