Seperating Text from Numbers in a cell

[MaryJ]

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

 

[Eduardo]

hi,

=LEFT(A1LEN(A1-5)

if this helps please click yes thanks

 

[Sean Timmons]

the below only (sort of) works if the address has 5 characters for the street
and does not account for the number separation.

 

[Eduardo]

Opps there is a comma missing

=LEFT(A1,LEN(A1-5)

 

[Ron Rosenfeld]

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Easily done with a VBA Macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub) as written, Select the range you wish to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that this Macro will not change any addresses that do not start with
digits. Also, if there happens to be a space after the initial digits, it will
not add an extra space.

================================
Option Explicit
Sub SplitAddr()
Dim rg As Range, c As Range
Dim re As Object

Set re = CreateObject("vbscript.regexp")
With re
.Pattern = "^(\d+)\s?"
.Global = False
End With

Set rg = Selection 'or Whatever range you want to process
For Each c In rg
c.Value = re.Replace(c.Value, "$1 ")
Next c
End Sub
=========================
--ron

 

[MaryJ]

Opps there is a comma  missing

=LEFT(A1,LEN(A1-5)






- Show quoted text -

Ok, I'm still kind of confused... there are different lengths of
numbers. Any chance I could get a little more help?

 

[MaryJ]

Easily done with a VBA Macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub) as written, Select the range you wish to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that this Macro will not change any addresses that do not start with
digits.  Also, if there happens to be a space after the initial digits,it will
not add an extra space.

================================
Option Explicit
Sub SplitAddr()
Dim rg As Range, c As Range
Dim re As Object

Set re = CreateObject("vbscript.regexp")
With re
    .Pattern = "^(\d+)\s?"
    .Global = False
End With

Set rg = Selection 'or Whatever range you want to process
For Each c In rg
    c.Value = re.Replace(c.Value, "$1 ")
Next c
End Sub
=========================
--ron

How do I "select the macro by name"?

 

[Ron Rosenfeld]

Easily done with a VBA Macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub) as written, Select the range you wish to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that this Macro will not change any addresses that do not start with
digits. Also, if there happens to be a space after the initial digits, it will
not add an extra space.

================================
Option Explicit
Sub SplitAddr()
Dim rg As Range, c As Range
Dim re As Object

Set re = CreateObject("vbscript.regexp")
With re
.Pattern = "^(\d+)\s?"
.Global = False
End With

Set rg = Selection 'or Whatever range you want to process
For Each c In rg
c.Value = re.Replace(c.Value, "$1 ")
Next c
End Sub
=========================
--ron

It occurred to me that you might want to have the number in one cell and the
street name in the adjacent cell. Instead of merely inserting a <space>
between the two as I did.

If that is the case, use this macro on your selection instead.

=========================
Sub ParseAddr()
Dim rg As Range, c As Range
Dim re As Object
Dim sTemp As String
Dim sAdr As String, sStreet As String

Set re = CreateObject("vbscript.regexp")
With re
.Pattern = "^(\d+)\s?([\S\s]*)$"
.Global = False
End With

Set rg = Selection 'or Whatever range you want to process
For Each c In rg
sTemp = c.Value
If re.test(sTemp) = True Then
c.Value = re.Replace(sTemp, "$1 ")
Else
c.Value = ""
End If
c.Offset(0, 1).Value = re.Replace(sTemp, "$2")
Next c
End Sub
===============================
--ron

 

[Sean Timmons]

He means select the macro named SplitAddr

That's the name of this macro as written.

 

[Ron Rosenfeld]

How do I "select the macro by name"?

What did you see when you did this part of my instructions:

The macro you wrote should have been listed in the box.

By "select by name", I mean to look in the box for the name of the macro you
just inserted. The name of the macro is that part after the word Sub and
before the "()".

So you should see, in that box, "SplitAddr"

The top portion of the Macro dialog box is labeled "Macro Name"

If the correct name is not already in there, you can either type it in, or move
your cursor over the name that is in the lower part of the box, and then click
your left mouse button (or the equivalent if you are not using a mouse).

Once you have the proper name in that box, (and assuming you have followed the
other instructions in my initial posting), then you should be able to select
the "Run" button by moving your cursor over to it, and pressing the left mouse
button.



--ron

 

[Teethless mama]

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))))+0)

 

[Sean Timmons]

Well, that's even easier than a macro... And if needed, assuming the below
lookup formula is in A2:

=right(A1,len(a1)-len(A2)) will get you the street name in A3

 

[Ron Rosenfeld]

Well, that's even easier than a macro... And if needed, assuming the below
lookup formula is in A2:

=right(A1,len(a1)-len(A2)) will get you the street name in A3

With this method, it would be prudent to add some error-checking to ensure the
string starts with a number. There are plenty of addresses around here that
consist just of a street name.
--ron

 

[David Biddulph]

.... and a parenthesis missing too?