Forum Discussion
I need to remove 4 digits in a file
Hi All,
Im working with EXCEL spreadsheets and have address and numerical data in a column, there is zip code data in column J that represents 9 digits and the column has upwards to 20K lines. I need to remove the last 4 digits in column J for the whole file. is there a formula or quick way to remove the data ??
Thank you
| 323242896 |
| 323260058 |
| 323261732 |
| 323271500 |
| 323273399 |
| 323276002 |
| 323280363 |
| 323283302 |
| 323318622 |
| 323331635 |
| 323333830 |
| 323335163 |
| 323335447 |
| 323342223 |
| 323360161 |
| 323401609 |
| 323402187 |
| 323404031 |
5 Replies
As variant
Data->Text to Columns->Fixed width->Create break on second step->Select Text or General, depends on needs, on third step->Finish
Select new column K -> Del
jim_p_1490 Removing the four right most characters of nine means you want to retain the five left most. Thus, I would suggest:
=LEFT(J1, 5)- Hello,
=RIGHT(J2,4)
Copy down the formulaThank you Abiola1
It didnt work
- If your codes are numbers rather than text then you could use this =LEFT(TEXT(A1,"General"),5)
You should also check out the Get Data (Power Query) functionality of Excel. It's perfect for this sort of data clean up
