Thread: Macro to Delete Spaces between text and numbers in a cell

I have a worksheet with a column K populated with over 13000 lines of descriptions as this.. e.g

BASKET PERF 254 X 254 X 30 MM
SCISSOR CVDMAYO HARRINGTON 230 MM
SCISSOR METZENBAUM CURVED 180 CM
SCISSOR LATERAL ANGLE POTTS-SMITH 55DEGREE S/S 180 MM
SCISSOR POTTS-DE-MARTEL 45 DEGREES STAINLESS/STEEL 185 CM


I want to make them:

BASKET PERF 254X254X30MM
SCISSOR CVDMAYO HARRINGTON 230MM
SCISSOR METZENBAUM CURVED 180CM
SCISSOR LATERAL ANGLE POTTS-SMITH 55DEGREE S/S 180MM
SCISSOR POTTS-DE-MARTEL 45 DEGREES STAINLESS/STEEL 185CM



Just to delete the spaces between the numbers and the unit of Measure,..


Can anyone help?

Thanks in Advance.

Will the unit of measure always be 2 characters? EDIT: This also depends on the unit of measure always being at the end of the line.

If so....

Dim ASweep integer
Dim UM, UN as string

For ASweep = 1 to Range("K65536").End(xlUp).Row    ' assuming your data starts on row 1 of column K and there's no blank cells.  If there are, just pick a value big enough to cover your whole data range.
     UM = cells(Asweep, 11).value
     UN = left$(UM, Len(UM) - 3) & right$(UM, 2)
     cells(Asweep, 11).value = UN
next ASweep

hi bigdee008@yahoo.com, please check attachment, run code "test". The code is based on the example rows provided.

HI SWOOP 99,... Im getting a syntax error with the code,..

Sub Magic()

Dim ASweep integer
Dim UM, UN As String

For ASweep = 1 To Range("K65536").End(xlUp).Row    ' assuming your data starts on row 1 of column K and there's no blank cells.  If there are, just pick a value big enough to cover your whole data range.
     UM = Cells(ASweep, 11).Value
     UN = Left$(UM, Len(UM) - 3) & Right$(UM, 2)
     Cells(ASweep, 11).Value = UN
Next ASweep

End Sub