Thread: Delete data after the first six chars of a cell

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

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...

Public Sub Example()
With Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("IF(ROW(" & .Address & "),LEFT(" & .Address & ",6))")
End With
End Sub

where original values reside in A1 onwards...

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).

In that case, please make thread [solved]