Test for char in string & separating if present

  • Thread starter Thread starter Ken Loomis
  • Start date Start date
K

Ken Loomis

The data I am working with lists the street name & unit number two different
ways.

Sometimes it looks like this:

MAIN ST#34

and sometimes it looks like this"

MAIN ST #34 (with the unit number in the next column)

What code could I use to test for the existence of '#' in a cell and if it
exists, separate them so the street name stays in the same cell and the unit
number, including the '#' is placed in the next column of the same row?

Thanks for any help or suggestions on this.

Ken Loomis
 
Hi
a solutioj without VBA: 'Data - Text to columns' and choose the '#' as
delimiter
 
Thanks. I did not know about that feature of Excel.

That strips out the text following the '#' and puts it in the next column,
but it wipes out the numbers that are in that column.

Since the unit number is in the street name column at times and in the unit
number column at other times, I need to preserve the unit numbers in the
destination column. It also removes the '#' which I need to have in the
final report.

That is a recordable operation, so I could add that to a macro (which I need
to do anyway) and do the following:

Insert a column
perform the Text to Columns (putting the stripped unit numbers into the
new column)
remove the '#'s from the each cell in the original unit column
for each cell in that new column
concatenate it with a "#" & the next cell in that row
delete the extra column

I could do that, but I was just wondering if there were a solution that
would do that a little more efficiently in VBA since this procedure needs to
be run several times a week.

Thanks,
Ken Loomis
 
assume the column with the unit numbers correctly separated is column B

Sub Tester1()
Dim iloc As Long, j As Long
Dim rng As Range, cell As Range
Dim cell1 As Range, sStr As String
Set rng = Columns(2).SpecialCells(xlBlanks)
For Each cell In rng
Set cell1 = cell.Offset(0, -1)
sStr = cell1.Value
iloc = InStr(1, sStr, "#", vbTextCompare) - 1
j = Len(sStr)
If iloc > 0 Then
cell1.Value = Trim(Left(sStr, iloc))
cell.Value = Trim(Right(sStr, j - iloc))
End If
Next


End Sub
 
Thanks, Tom. That is slick. And I even am getting to where I can understand
it.

But, now I find that most of those cells that I thought were blank in that
column that is to receive the unit numbers, actual contains a single blank.

Any ideas about a quick way to strip those out with out disturbing the cells
that contain something like "# 123"?

Ken Loomis
 
Sub Tester1()
Dim iloc As Long, j As Long
Dim rng As Range, cell As Range
Dim cell1 As Range, sStr As String
Columns(2).Replace What:=" ", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
For Each cell In rng
Set cell1 = cell.Offset(0, -1)
sStr = cell1.Value
iloc = InStr(1, sStr, "#", vbTextCompare) - 1
j = Len(sStr)
If iloc > 0 Then
cell1.Value = Trim(Left(sStr, iloc))
cell.Value = Trim(Right(sStr, j - iloc))
End If
Next
End if
End Sub
 
Back
Top