Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto delete the first X digits in a cell number ?
Hi there, I have the following in Cell A1 to A3. ABC50293.01 ABC10006 ABC1000003 I want to automatically delete the first 4 digits in each cell, so that it will become: 0293.01 0006 000003 How can i do that? Thanks a lot. cheers:) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto delete the first X digits in a cell number ?
hi,
you can remove the frirst 4 characters with the following formula... =MID(A1,5,10) copy the formula and paste as values regards FSt1 " wrote: Hi there, I have the following in Cell A1 to A3. ABC50293.01 ABC10006 ABC1000003 I want to automatically delete the first 4 digits in each cell, so that it will become: 0293.01 0006 000003 How can i do that? Thanks a lot. cheers:) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto delete the first X digits in a cell number ?
Several ways, here's a couple:
Probably the easiest: Insert a column to the right of the current column and use Text to Columns under Data, with the fixed column width option, to separate the entry into two columns. The first containing just the first four characters and the second everything else. Or: Use this formula in another cell to give you the data starting with the 5th character: =MID(H2,5,LEN(H2)-4) " wrote: Hi there, I have the following in Cell A1 to A3. ABC50293.01 ABC10006 ABC1000003 I want to automatically delete the first 4 digits in each cell, so that it will become: 0293.01 0006 000003 How can i do that? Thanks a lot. cheers:) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to auto delete the first X digits in a cell number ?
Sorry about the h's, forgot to fix for yours...
=MID(A1,5,LEN(A1)-4) You can enter it with a fixed length, =MID(A1,5,10) but letting excel check for the length LEN(A1) minus the 4 characters at the beginning, means you'll never accidently cut stuff off the end if a number ends up being longer than you anticipated. "BoniM" wrote: Several ways, here's a couple: Probably the easiest: Insert a column to the right of the current column and use Text to Columns under Data, with the fixed column width option, to separate the entry into two columns. The first containing just the first four characters and the second everything else. Or: Use this formula in another cell to give you the data starting with the 5th character: =MID(H2,5,LEN(H2)-4) " wrote: Hi there, I have the following in Cell A1 to A3. ABC50293.01 ABC10006 ABC1000003 I want to automatically delete the first 4 digits in each cell, so that it will become: 0293.01 0006 000003 How can i do that? Thanks a lot. cheers:) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete the 4 rightmost digits from a cell | Excel Worksheet Functions | |||
Delete 4 to 5 digits from left in a cell | Excel Discussion (Misc queries) | |||
Delete 4 to 5 digits from left in a cell | Excel Discussion (Misc queries) | |||
Delete 4 to 5 digits from left in a cell | Excel Discussion (Misc queries) | |||
calculate using last four digits of number in cell | Excel Worksheet Functions |