Macro To Amend Data In A Column

[Saruman]

I hope that someone can help me with a problem I have inherited with an
Excel 2003 file.

When the file was created, a column was sized and then the data was made to
fit the column by adding spaces and using word wrap to align the data on
multiple rows instead of using the Alt+Enter. I now need to remove all the
extra spaces and replace them with Alt+enter.

There are currently approx 1800 rows, some of which contain no data and are
used as separators between each section. The data is in Column H from Cell
H3 downwards. The data is the name of a data attribute followed by a number,
which is the unique reference number in our system of that attribute.

eg Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

I manually counted some of the spaces and they can range from 15 to 35 of
them between each data attribute.

Can any one please assist with a macro to remove all the extra
spaces(Char32) and replace them with a single Alt+enter(Char10)? I am an
experienced user of Excel with little programming knowledge, but do know
where to place the macro code. I have tried using ASAP Utilities to carry
out the task but there is still a lot of manual work required afterwards and
I am sure this could be done more efficiently with a macro.

Thanks in advance.

 

[Gord Dibben]

Try this macro on the selected column.

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Cell.Replace What:=" ", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub


Gord Dibben MS Excel MVP

 

[CLR]

As you know, ASAP Utilities will get rid of all the extra spaces for you,
then the regular Find&Replace will change them to CR's.......use Alt32 for
the space, and Alt010 (both numbers from the KEYPAD) for the
CR.......ReplaceAll

Vaya con Dios,
Chuck, CABGx3

 

[Saruman]

Gordon,

Thanks for a quick reply. This actually removes ALL the spaces and leaves a
single word or number on each line.

I need to have the whole description and the number on a single line as per
the data in the example.

Thanks.

Saruman

 

[Saruman]

Chuck,

Thanks for the quick response. I can get ASAP Utilities to do the same as
the result from Gordons Macro. It leaves a single item on each line which
will then require attention to reformat them as a complete string on each
line. There are 1800 lines with at least 2 complete line items in each cell
which means a lot of manual work.

Maybe thats inevitable.

Thanks.

Saruman

 

[Gord Dibben]

I guess I did not understand your original post and example and I might continue
to not understand.

I thougfht you wanted extra spaces removed, which my macro does.

It also adds a linefeed between each word which I assume you didn't want.

Is this all in a single cell along with extra spaces to achieve proper spacing
for wordwrap?

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

And you want the same thing only with extra spaces removed and linefeeds after
508 and 505?

I would certainly have to think about that code for a while.

Hopefully one of the brighter lights can help.


Gord

 

[Saruman]

Gord,

Apologies for the misunderstanding.

The current data in a single cell is one long string of characters with many
spaces between the start of each name.It is like the example below. The
Column width was then adjusted so that the Names lined up on the left side
of the cell with a wrap text added that made the data appear to be on 3
lines..

Estimated Repair Time 508 Repair Action
Description 505 Repair action Code 1045



I need it to look like the original data example also in a single cell so
there is the Data Attribute Name and the Number on one line with normal
single spaces between the text and the number, the multiple extra spaces
between the number and the next name would need to be removed and a Line
Feed would need to be added, such as the Alt+Enter function in Excel.

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045



If you are unable to assist, then thank you for your efforts.
--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------

I guess I did not understand your original post and example and I might continue
to not understand.

I thougfht you wanted extra spaces removed, which my macro does.

It also adds a linefeed between each word which I assume you didn't want.

Is this all in a single cell along with extra spaces to achieve proper spacing
for wordwrap?

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

And you want the same thing only with extra spaces removed and linefeeds after
508 and 505?

I would certainly have to think about that code for a while.

Hopefully one of the brighter lights can help.


Gord

Gordon,

Thanks for a quick reply. This actually removes ALL the spaces and leaves a
single word or number on each line.

I need to have the whole description and the number on a single line as per
the data in the example.

Thanks.

Saruman
---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------

Try this macro on the selected column.

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Cell.Replace What:=" ", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I _think_ that this is what you want.

But it does depend on finding a number that is followed by a space character.

Select your range to fix and try this macro. Try it against some test data
before you trust it!

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myStr As String
Dim cCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a nice range"
Exit Sub
End If

For Each myCell In myRng.Cells
myStr = myCell.Value
myStr = Replace(myStr, vbLf, " ")
myStr = Application.Trim(myStr)

For cCtr = Len(myStr) To 2 Step -1
If Mid(myStr, cCtr, 1) = Space(1) Then
If IsNumeric(Mid(myStr, cCtr - 1, 1)) Then
Mid(myStr, cCtr, 1) = vbLf
End If
End If
Next cCtr
myCell.Value = myStr
Next myCell

End Sub

Gord,

Apologies for the misunderstanding.

The current data in a single cell is one long string of characters with many
spaces between the start of each name.It is like the example below. The
Column width was then adjusted so that the Names lined up on the left side
of the cell with a wrap text added that made the data appear to be on 3
lines..

Estimated Repair Time 508 Repair Action
Description 505 Repair action Code 1045

I need it to look like the original data example also in a single cell so
there is the Data Attribute Name and the Number on one line with normal
single spaces between the text and the number, the multiple extra spaces
between the number and the next name would need to be removed and a Line
Feed would need to be added, such as the Alt+Enter function in Excel.

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

If you are unable to assist, then thank you for your efforts.
--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------

I guess I did not understand your original post and example and I might continue
to not understand.

I thougfht you wanted extra spaces removed, which my macro does.

It also adds a linefeed between each word which I assume you didn't want.

Is this all in a single cell along with extra spaces to achieve proper spacing
for wordwrap?

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

And you want the same thing only with extra spaces removed and linefeeds after
508 and 505?

I would certainly have to think about that code for a while.

Hopefully one of the brighter lights can help.


Gord

 

[Gord Dibben]

I understand but am having a hard time with my limited skills to locate the last
digit in 505 where we would add a linefeed.

Also at last digit from 505


Gord

 

[Saruman]

Thanks Dave,

That works a treat on some test data that I have at home here, will try on a
copy of the original tomorrow. Will post back after then.

Thanks for your help!

Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------

I _think_ that this is what you want.

But it does depend on finding a number that is followed by a space character.

Select your range to fix and try this macro. Try it against some test data
before you trust it!

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myStr As String
Dim cCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a nice range"
Exit Sub
End If

For Each myCell In myRng.Cells
myStr = myCell.Value
myStr = Replace(myStr, vbLf, " ")
myStr = Application.Trim(myStr)

For cCtr = Len(myStr) To 2 Step -1
If Mid(myStr, cCtr, 1) = Space(1) Then
If IsNumeric(Mid(myStr, cCtr - 1, 1)) Then
Mid(myStr, cCtr, 1) = vbLf
End If
End If
Next cCtr
myCell.Value = myStr
Next myCell

End Sub

Gord,

Apologies for the misunderstanding.

The current data in a single cell is one long string of characters with many
spaces between the start of each name.It is like the example below. The
Column width was then adjusted so that the Names lined up on the left side
of the cell with a wrap text added that made the data appear to be on 3
lines..

Estimated Repair Time 508 Repair Action
Description 505 Repair action Code 1045

I need it to look like the original data example also in a single cell so
there is the Data Attribute Name and the Number on one line with normal
single spaces between the text and the number, the multiple extra spaces
between the number and the next name would need to be removed and a Line
Feed would need to be added, such as the Alt+Enter function in Excel.

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

If you are unable to assist, then thank you for your efforts.

-

All Outgoing Mail Scanned By Symantec Antivirus 10
--------------------------------------------------------------------------

-

I guess I did not understand your original post and example and I

might
continue

to not understand.

I thougfht you wanted extra spaces removed, which my macro does.

It also adds a linefeed between each word which I assume you didn't want.

Is this all in a single cell along with extra spaces to achieve proper spacing
for wordwrap?

Estimated Repair Time 508
Repair Action Description 505
Repair action Code 1045

And you want the same thing only with extra spaces removed and

linefeeds
after

508 and 505?

I would certainly have to think about that code for a while.

Hopefully one of the brighter lights can help.


Gord

 

[Saruman]

Gord,

Thanks for your help, Dave Peterson has a solution.

Many thanks for your time and assistance.

Saruman

 

[Dave Peterson]

Just jumping in...

That's one of the good things about a collaborative effort. I read your posts
and didn't understand them. Between Gord's questions and your clarifications,
you may(!) have gotten something that works <bg>.

 

[Gord Dibben]

As I said.........one of the brighter lights........

Thanks from me also Dave.

I think I have maxed out......I can work it all out until you get down to the
"For cCtr = Len(myStr) To 2 Step -1" part.

Then I start to mumble and fall over<g>

Back to fielding Tools>Options postings.


Gord

 

[Dave Peterson]

How about this one--it might be quicker if there are lots of cells to be fixed:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim cCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a nice range"
Exit Sub
End If

If myRng.Cells.Count < 2 Then
Set myRng = Union(myRng, _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1))
End If

myRng.Replace what:=vbLf, _
replacement:=Space(1), _
lookat:=xlPart, _
MatchCase:=False

For Each myCell In myRng.Cells
myCell.Value = Application.Trim(myCell.Value)
Next myCell

For cCtr = 0 To 9
myRng.Replace what:=cCtr & " ", _
replacement:=cCtr & vbLf, _
lookat:=xlPart, _
MatchCase:=False
Next cCtr

End Sub

It looks like every string that ends with a number (followed by a space) is
converted to that number followed by alt-enter.

I couldn't think of a way to do an Edit|Replace to get rid of any leading or
trailing spaces--so I still had to loop through each cell using
application.trim().

 

[Saruman]

Dave,

Code works. Many thanks to you and the other posters to my problem. This
will save me hours of work.

Thanks again, post can now be closed.

Saruman