Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
I have a column and all I need is the last 5 characters in this field.
Problem is that this field has various text lengths. How can I get the last 5 characters and delete the rest. My spreadsheet has 1,000 or so lines. This Column looks like this: AESGROUPGR123 AESGROUPGR457 AESGROUPGR236 SECTIOGR046 MSGTYHSYHSYHGR123 MSGTYHSYYHGR123 My first thought was to make ths column flush right in Text to columns, but I do not see that making it flush right is an option. Ideas? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
Maybe you could use a helper column and use this formula
Change A1 what you need and the 5 is for the first 5 characters from the right. =RIGHT(A1,5) "Allan Fischler" wrote: I have a column and all I need is the last 5 characters in this field. Problem is that this field has various text lengths. How can I get the last 5 characters and delete the rest. My spreadsheet has 1,000 or so lines. This Column looks like this: AESGROUPGR123 AESGROUPGR457 AESGROUPGR236 SECTIOGR046 MSGTYHSYHSYHGR123 MSGTYHSYYHGR123 My first thought was to make ths column flush right in Text to columns, but I do not see that making it flush right is an option. Ideas? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
On Oct 8, 8:21 pm, Allan Fischler wrote:
I have a column and all I need is the last 5 characters in this field. Problem is that this field has various text lengths. How can I get the last 5 characters and delete the rest. My spreadsheet has 1,000 or so lines. This Column looks like this: AESGROUPGR123 AESGROUPGR457 AESGROUPGR236 SECTIOGR046 MSGTYHSYHSYHGR123 MSGTYHSYYHGR123 My first thought was to make ths column flush right in Text to columns, but I do not see that making it flush right is an option. Ideas? Thanks. Can you just use a right formula? =RIGHT(A2,5) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to columns
You could use VBA code to get the results your looking for
AESGROUPGR123 will be GR123 AESGROUPGR457 will be GR457 Sub fivecharacters() lastcell = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To lastcell Range("A" & i).Value = Right(Range("A" & i).Value, 5) Next End Sub "Allan Fischler" wrote: I have a column and all I need is the last 5 characters in this field. Problem is that this field has various text lengths. How can I get the last 5 characters and delete the rest. My spreadsheet has 1,000 or so lines. This Column looks like this: AESGROUPGR123 AESGROUPGR457 AESGROUPGR236 SECTIOGR046 MSGTYHSYHSYHGR123 MSGTYHSYYHGR123 My first thought was to make ths column flush right in Text to columns, but I do not see that making it flush right is an option. Ideas? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Excel is automatically doing "text to columns" upon paste text. | Excel Discussion (Misc queries) | |||
Linking text columns with text and data columns | Excel Worksheet Functions |