Remove Text

[Brandon]

I need a function to remove text from a cell and leave 5 digits at the very
end of the string.

For example:

Company One 11111
Company 2 22222
Com Three 33333
Co. Four 44444
Co. 5 55555

What is to the left of the numbers that I need to keep varies in length. So
I need to remove everything that isn't the furthest right 5 characters in the
string.

Any help is greatly appreciated. Thank you in advance.

Brandon

 

[Teethless mama]

=RIGHT(A1,5)
return text value

=--RIGHT(A1,5)
return numeric value

 

[Pete_UK]

If it is always 5 digits, then you can use this:

=RIGHT(A1,5)

If you need it as a number, then do this:

=--RIGHT(A1,5)

or:

=RIGHT(A1,5)*1

Hope this helps.

Pete

 

[Gaffnr]

Hi Brandon
You can do =right(A1,5) in the column next to it? This returns the last 5
characters.

Rob

 

[Glenn]

I need a function to remove text from a cell and leave 5 digits at the very
end of the string.

For example:

Company One 11111
Company 2 22222
Com Three 33333
Co. Four 44444
Co. 5 55555

What is to the left of the numbers that I need to keep varies in length. So
I need to remove everything that isn't the furthest right 5 characters in the
string.

Any help is greatly appreciated. Thank you in advance.

Brandon

=RIGHT(A1,5)

 

[Jacob Skaria]

The below will return the last 5 digit numeric...(only if that is a numeric
value..)

With your data in cell A1;try the formula in cell b1

=IF(ISNUMBER(--RIGHT(A1,5)),--RIGHT(A1,5),"")

If this post helps click Yes

 

[Gord Dibben]

Without formulas.

This method from Per Jenssen

TextToColumns function on the data menu is what you need.

Use the wizard, space as delimiter skip first and second column, and set
destination to next column.

Hopes this helps.
....
Per


Gord Dibben MS Excel MVP

 

[Gord Dibben]

ooops

Make that Per Jessen


Gord

Without formulas.

This method from Per Jenssen

TextToColumns function on the data menu is what you need.

Use the wizard, space as delimiter skip first and second column, and set
destination to next column.

Hopes this helps.
...
Per


Gord Dibben MS Excel MVP