MID, LEFT, RIGHT help
PERFECT !!!
Thanks Rick
"Rick Rothstein" wrote:
Whoops... I forgot the length of the original text. Try this...
E2: =MID(A1,LEN(D1)+2,LEN(A1)-LEN(F1)-LEN(D1)-2)
--
Rick (MVP - Excel)
"mattg" wrote in message
...
The formulas for columns D and F are fine but the formula for column E
only
returns only 3 or 4 charatcers of the street name.
"Rick Rothstein" wrote:
Since you street names might be made up of more than one word, you can't
use
Excel's Text-To-Columns feature; however, these formulas should work...
D2: =LEFT(A2,FIND(" ",A2)-1)
E2: =MID(A2,LEN(D2)+2,LEN(F2)+LEN(D2)-1)
F2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))
--
Rick (MVP - Excel)
"mattg" wrote in message
...
Hi,
I addresses in column C, "123 Main St"(quotes not in actual cell) for
example. How can get the house number in column D, the street name in
Column
E and the suffix(ST, RD , AVE, etc.) in Column F? The number of
characters
can vary with each cell. Some are all uppercase some are not.
I tried this =MID(C2,FIND(" ",C2)+1,FIND(" ",C2)) to capture the street
name
but it doesn't work properly.
Any ideas?
Thanks,
Matt
|