| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Seperating Text from Numbers in a cell
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
hi,
=LEFT(A1LEN(A1-5) if this helps please click yes thanks "MaryJ" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
the below only (sort of) works if the address has 5 characters for the street
and does not account for the number separation. "Eduardo" wrote: hi, =LEFT(A1LEN(A1-5) if this helps please click yes thanks "MaryJ" wrote: 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
Opps there is a comma missing
=LEFT(A1,LEN(A1-5) "Eduardo" wrote: hi, =LEFT(A1LEN(A1-5) if this helps please click yes thanks "MaryJ" wrote: 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
On Sep 1, 10:21*am, Eduardo wrote:
Opps there is a comma *missing =LEFT(A1,LEN(A1-5) "Eduardo" wrote: hi, =LEFT(A1LEN(A1-5) if this helps please click yes thanks "MaryJ" wrote: 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!- Hide quoted text - - 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)
"MaryJ" wrote: 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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
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 "Teethless mama" wrote: =LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0) "MaryJ" wrote: 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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
On Tue, 1 Sep 2009 09:29:01 -0700, Sean Timmons
wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Seperating Text from Numbers in a cell
.... and a parenthesis missing too?
-- David Biddulph "Eduardo" wrote in message ... Opps there is a comma missing =LEFT(A1,LEN(A1-5) "Eduardo" wrote: hi, =LEFT(A1LEN(A1-5) if this helps please click yes thanks "MaryJ" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel, seperating numbers and text, macro | Excel Discussion (Misc queries) | |||
| Seperating numbers from text | Excel Discussion (Misc queries) | |||
| Seperating text within a cell | Excel Discussion (Misc queries) | |||
| seperating text in one cell to multiple cells | Excel Discussion (Misc queries) | |||
| Seperating of Text in one cell into two columns | Excel Worksheet Functions | |||
Linear Mode
