Hi All,

I need to remove the first 9 chracters in every cell of column A of my
spreadsheet. What's the best way to do that?

My spreadsheet is currently huge, lots of data, graphs, functions and
defined names, all of which make it quite slow to load and manipulate.
I would like to avoid if possible making this too much worse. Not sure
if that will change the suggested best solution to my question above.

All help is much appreciated


--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
View this thread: http://www.excelforum.com/showthread...hreadid=525610

For safety reasons make a backup copy!
Enter this formula in the first line of an empty helper column:
=MID(A1,10,255)
Fill down as necessary!

Select the helper column/Copy
Select column A/pastespecial-values
Clear helper column!

Regards,
Stefi


€žcoa01gsb€� ezt Ã*rta:


Hi All,

I need to remove the first 9 chracters in every cell of column A of my
spreadsheet. What's the best way to do that?

My spreadsheet is currently huge, lots of data, graphs, functions and
defined names, all of which make it quite slow to load and manipulate.
I would like to avoid if possible making this too much worse. Not sure
if that will change the suggested best solution to my question above.

All help is much appreciated


--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
View this thread: http://www.excelforum.com/showthread...hreadid=525610

On Thu, 23 Mar 2006 03:35:37 -0600, coa01gsb
wrote:


Hi All,

I need to remove the first 9 chracters in every cell of column A of my
spreadsheet. What's the best way to do that?

My spreadsheet is currently huge, lots of data, graphs, functions and
defined names, all of which make it quite slow to load and manipulate.
I would like to avoid if possible making this too much worse. Not sure
if that will change the suggested best solution to my question above.

All help is much appreciated

Backup your workbook first!

In a helper column, enter the formula:

=REPLACE(A1,1,9,"")

Copy/Drag down as far as required.

Then,
Select the cells in the helper column
Edit/Copy
Select A1
Edit/Paste Special Values

Then Delete Helper Column.


--ron

An alternative formula in the helper column is:

=RIGHT(A1,LEN(A1)-9)

As Ron suggests, you can then:

Copy/Drag down as far as required.
Then,
Select the cells in the helper column
Edit/Copy
Select A1
Edit/Paste Special Values
Then Delete Helper Column.

Hope this helps.

Pete

A manual way to do this is to use the Text to Columns command in the Data
Menu.

Work on a backup to get used to the method.

Select the single column range with the text you wish to change.
Data\Text to Columns
Fixed Width, Next
Create a line at the break point you need, Next
For Column 1
Skip
For Column 2
General
You can select a different destination, The default is to overwrite.
Finish.

This should leave you with the Data modifed as required and no additional
formulae

hth RES

Cheers people, plenty of ideas there.


--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
View this thread: http://www.excelforum.com/showthread...hreadid=525610