Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove 4 of 9 numbers from a cell for the whole column?
Example...
1234-56789 becomes 56789 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove 4 of 9 numbers from a cell for the whole column?
Insert column to right of the column containing your data
Data==Text-to-Columns=Delimited=Other ..enter "-" (no quotes) then Finish. Delete redundant data i.e one containing the 4 digits. HTH "RISXRAY" wrote: Example... 1234-56789 becomes 56789 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove 4 of 9 numbers from a cell for the whole colu
I formated the "-" in the cell by using custom formating (0000-00000), so
when I do the delimiting, it doesn't show the "-". My orginal number is whole 123456789. "Toppers" wrote: Insert column to right of the column containing your data Data==Text-to-Columns=Delimited=Other ..enter "-" (no quotes) then Finish. Delete redundant data i.e one containing the 4 digits. HTH "RISXRAY" wrote: Example... 1234-56789 becomes 56789 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove 4 of 9 numbers from a cell for the whole colu
Aha.... I removed the formating and did the conversion by a "fixed width".
Works great. Thank you for the lead... "Toppers" wrote: Insert column to right of the column containing your data Data==Text-to-Columns=Delimited=Other ..enter "-" (no quotes) then Finish. Delete redundant data i.e one containing the 4 digits. HTH "RISXRAY" wrote: Example... 1234-56789 becomes 56789 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove 4 of 9 numbers from a cell for the whole colu
If your number is in A2, try this in a blank column:
=LEFT(A2,5) which will return the values as text - if you want them as numbers, try this: =VALUE(LEFT(A2,5)) Copy the formula down for as many rows as you have data in column A. Hope this helps. Pete RISXRAY wrote: I formated the "-" in the cell by using custom formating (0000-00000), so when I do the delimiting, it doesn't show the "-". My orginal number is whole 123456789. "Toppers" wrote: Insert column to right of the column containing your data Data==Text-to-Columns=Delimited=Other ..enter "-" (no quotes) then Finish. Delete redundant data i.e one containing the 4 digits. HTH "RISXRAY" wrote: Example... 1234-56789 becomes 56789 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove 4 of 9 numbers from a cell for the whole colu
Sorry, you would need to use RIGHT rather than LEFT, but as you have
found another way to do it, it's irrelevant. Pete Pete_UK wrote: If your number is in A2, try this in a blank column: =LEFT(A2,5) which will return the values as text - if you want them as numbers, try this: =VALUE(LEFT(A2,5)) Copy the formula down for as many rows as you have data in column A. Hope this helps. Pete RISXRAY wrote: I formated the "-" in the cell by using custom formating (0000-00000), so when I do the delimiting, it doesn't show the "-". My orginal number is whole 123456789. "Toppers" wrote: Insert column to right of the column containing your data Data==Text-to-Columns=Delimited=Other ..enter "-" (no quotes) then Finish. Delete redundant data i.e one containing the 4 digits. HTH "RISXRAY" wrote: Example... 1234-56789 becomes 56789 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I DELETE NUMBERS IN A COLUMN out of each cell | New Users to Excel | |||
How can I remove diff. numbers from a cell combined with text | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions |