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

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

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

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

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

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