Delete data after the first six chars of a cell

**woodsonline** · 08-30-2009, 06:42 AM

Hi all, I am trying to delete anything after the first six characters in a cell.

For example:

134383922
1012586790
1752039458
1293947
13932881137

As you can see, the numbers are of different lengths so i can't (seem) to use the function that takes a set amount OFF either the start or end (can't remember name of function).

Basically the list above needs to come out as:

134383
101258
175203
129394
139328

Which is the the first six chars of any cell.

I can do it by making the column width show only those chars but i wonder if theres a better way...

Thanks as usual, in advance....

Steve Quinn
Woods of Morecambe Ltd
www.woods-online.co.uk

**DonkeyOte** · 08-30-2009, 06:48 AM

It's not clear if you want to overwrite the original values or have the six digit values populated in adjacent cell(s) via formulae, eg:

B1: =0+LEFT(A1,6)
copied down

If you want to overwrite the original values then you're looking at VBA...

Please Login or Register  to view this content.

where original values reside in A1 onwards...

**woodsonline** · 08-30-2009, 07:15 AM

excellent, thats perfect.

It didnt matter actually whether to write over or into another cell, but in this case, the easier copy to another cell was best.

by the way, not sure why, but

=LEFT(A1,6)

seemed to work a bit better because for some reason, your version

=0+LEFT(A1,6)

gave me strange results in the cell...

Thanks again though, i thought there would be a perfectly simple way to do this...

Steve.

**DonkeyOte** · 08-30-2009, 07:34 AM

The 0+ was used to coerce the 6 string value to a number... based on your sample values I had presumed all strings in A1 to be numeric (no leading zeroes) - using without the 0+ just means your results are numbers stored as text (the 0+ would fail if the values in A were not numeric).