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

--
Regards
Frank Kabel
Frankfurt, Germany

"Ken Loomis" schrieb im Newsbeitrag
...
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

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

"Frank Kabel" wrote in message
...
Hi
a solutioj without VBA: 'Data - Text to columns' and choose the '#' as
delimiter

--
Regards
Frank Kabel
Frankfurt, Germany

"Ken Loomis" schrieb im Newsbeitrag
...
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

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


--
Regards,
Tom Ogilvy


"Ken Loomis" wrote in message
...
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

"Frank Kabel" wrote in message
...
Hi
a solutioj without VBA: 'Data - Text to columns' and choose the '#' as
delimiter

--
Regards
Frank Kabel
Frankfurt, Germany

"Ken Loomis" schrieb im Newsbeitrag
...
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

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

"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy


"Ken Loomis" wrote in message
...
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

"Frank Kabel" wrote in message
...
Hi
a solutioj without VBA: 'Data - Text to columns' and choose the '#' as
delimiter

--
Regards
Frank Kabel
Frankfurt, Germany

"Ken Loomis" schrieb im Newsbeitrag
...
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

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

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy


"Ken Loomis" wrote in message
...
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

"Frank Kabel" wrote in message
...
Hi
a solutioj without VBA: 'Data - Text to columns' and choose the '#'
as
delimiter

--
Regards
Frank Kabel
Frankfurt, Germany

"Ken Loomis" schrieb im Newsbeitrag
...
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