| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing large amount of Data
I would like to simply change a large number of records in a spreadsheet
without having to change each one individually. Here is my example: Befo 21-101-38468-00-00 Change to: 21101384680000 How can I do this? There are over 14,000 records Thank you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing large amount of Data
Edit Replace
In the Find what field put - In the Replace with field put nothing Touch Replace All -- Gary's Student gsnu200701 "glnbnz" wrote: I would like to simply change a large number of records in a spreadsheet without having to change each one individually. Here is my example: Befo 21-101-38468-00-00 Change to: 21101384680000 How can I do this? There are over 14,000 records Thank you |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing large amount of Data
edit / replace / replace - with: leave it blank
"glnbnz" wrote: I would like to simply change a large number of records in a spreadsheet without having to change each one individually. Here is my example: Befo 21-101-38468-00-00 Change to: 21101384680000 How can I do this? There are over 14,000 records Thank you |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing large amount of Data
the replace function only solves part of your problem because it will leave
a blank space between digits anyways. After you use the replace option you want to sequentally extract groups of digits and concatenate them in one cell: CELL B1: 222 0000 CELL C1: =LEFT(B1,3) ......It will return 222 CELL D1: =RIGHT(B1,4) .....it will return 0000 CELL F1: =CONCATENATE(C1,D1) ...it will return 2220000 if you have longer sequences like 12 55 888 666 4748 just play with the left and right function. "Marvin P. Winterbottom" wrote: edit / replace / replace - with: leave it blank "glnbnz" wrote: I would like to simply change a large number of records in a spreadsheet without having to change each one individually. Here is my example: Befo 21-101-38468-00-00 Change to: 21101384680000 How can I do this? There are over 14,000 records Thank you |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing large amount of Data
No blank space if you replace "-" with nothing.
You will have to re-format the cell as number because the editreplace will leave the number in Scientific Notation. Gord Dibben MS Excel MVP On Fri, 12 Jan 2007 11:19:00 -0800, GAPM wrote: the replace function only solves part of your problem because it will leave a blank space between digits anyways. After you use the replace option you want to sequentally extract groups of digits and concatenate them in one cell: CELL B1: 222 0000 CELL C1: =LEFT(B1,3) ......It will return 222 CELL D1: =RIGHT(B1,4) .....it will return 0000 CELL F1: =CONCATENATE(C1,D1) ...it will return 2220000 if you have longer sequences like 12 55 888 666 4748 just play with the left and right function. "Marvin P. Winterbottom" wrote: edit / replace / replace - with: leave it blank "glnbnz" wrote: I would like to simply change a large number of records in a spreadsheet without having to change each one individually. Here is my example: Befo 21-101-38468-00-00 Change to: 21101384680000 How can I do this? There are over 14,000 records Thank you |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing large amount of Data
yep, you are right, the first time didnt work for me.
"Gord Dibben" wrote: No blank space if you replace "-" with nothing. You will have to re-format the cell as number because the editreplace will leave the number in Scientific Notation. Gord Dibben MS Excel MVP On Fri, 12 Jan 2007 11:19:00 -0800, GAPM wrote: the replace function only solves part of your problem because it will leave a blank space between digits anyways. After you use the replace option you want to sequentally extract groups of digits and concatenate them in one cell: CELL B1: 222 0000 CELL C1: =LEFT(B1,3) ......It will return 222 CELL D1: =RIGHT(B1,4) .....it will return 0000 CELL F1: =CONCATENATE(C1,D1) ...it will return 2220000 if you have longer sequences like 12 55 888 666 4748 just play with the left and right function. "Marvin P. Winterbottom" wrote: edit / replace / replace - with: leave it blank "glnbnz" wrote: I would like to simply change a large number of records in a spreadsheet without having to change each one individually. Here is my example: Befo 21-101-38468-00-00 Change to: 21101384680000 How can I do this? There are over 14,000 records Thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro question | Excel Worksheet Functions | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| Condensing a large data dump | Excel Discussion (Misc queries) | |||
| Changing entire column data | Excel Discussion (Misc queries) | |||
| how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Hybrid Mode
