Edit No Format

[Guest]

I have a spreadsheet that Has a column of No's that look like this AB 12 45 87 B the column has about 30,000 No 's in it. I want the No's to look like AB124587B so that they do not have the spaces in, any way of doing this un excel.

 

[Tornados]

If it's always the same format you can do it by using a combination of
the left(),mid(), and right() functions.. Then use the & sign to fix
it....

example :

a1: ab 12

a2: left(a1,2) & right(a1,2)

a2 then says: ab12

there might be a function that just removes all spaces though... trim()
only works for opening and ending spaces.

 

[Ken Wright]

Select the column, then

Edit / Replace / 'replace what = a space (ie hit the space bar once), 'replace
with' = blank (ie leave it blank)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all
----------------------------------------------------------------------------

I have a spreadsheet that Has a column of No's that look like this AB 12 45 87

B the column has about 30,000 No 's in it. I want the No's to look like
AB124587B so that they do not have the spaces in, any way of doing this un
excel.

 

[Peter Atherton]

-----Original Message-----
I have a spreadsheet that Has a column of No's that look

like this AB 12 45 87 B the column has about 30,000 No 's
in it. I want the No's to look like AB124587B so that they
do not have the spaces in, any way of doing this un excel.

.

Mani

Change the references to the following code as neccessary.
It is written to work on column B from row 2

Option Explicit

Dim r As Long, col As Integer

Sub clearSpaces()
Dim rng As Range
Dim c
Dim n As Integer, l As Integer
Range("B2").Select 'Change this to suit it should be
the column with part numbers
r = Application.WorksheetFunction.CountA(Range("B:B"))
col = ActiveCell.Column
Set rng = Range(Cells(2, col), Cells(r, col))
For Each c In rng
c.Replace What:=" ", Replacement:="", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False
Next c
End Sub

Regards
Peter