Remove first few characters

C

coa01gsb

Hi All,

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

My spreadsheet is currently huge, lots of data, graphs, functions an
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 sur
if that will change the suggested best solution to my question above.

All help is much appreciate
 
S

Stefi

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:
 
R

Ron Rosenfeld

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
Click to expand...

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
 
P

Pete_UK

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.
Click to expand...

Hope this helps.

Pete
 
R

Robert_Steel

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
 
Top