| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Text
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
=RIGHT(A1,5)
return text value =--RIGHT(A1,5) return numeric value "Brandon" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
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 On Nov 10, 3:38*pm, Brandon wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
Hi Brandon
You can do =right(A1,5) in the column next to it? This returns the last 5 characters. Rob -- Rob Gaffney "Brandon" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
Brandon wrote:
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
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 --------------- Jacob Skaria "Brandon" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
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 On Tue, 10 Nov 2009 07:38:02 -0800, Brandon wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove Text
ooops
Make that Per Jessen Gord On Tue, 10 Nov 2009 11:21:30 -0800, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Tue, 10 Nov 2009 07:38:02 -0800, Brandon wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text - Remove text Item No.99 (First 2 Chars) and move to end | Excel Discussion (Misc queries) | |||
| Easiest way to remove text from a cell that has text and numbers? | Excel Discussion (Misc queries) | |||
| Filter text in a column by its Indent, to remove certain text | Excel Discussion (Misc queries) | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Linear Mode
