Is there a way to separate the mailing street from Mailing Address. I like the result as what is shown in Column B (Mailing Street).
| Mailing Address 2 | Mailing Street |
|---|---|
| %BISI SHIRLEY V-EXECUTRIX 23 WHITNEY LN | 23 WHITNEY LN |
| %BLOOMENKRANZ RICHARD 3414 GLACIER RIDGE RD | 3414 GLACIER RIDGE RD |
| %MCDERMOTT JOSEPH M 859 W 47TH ST | 859 W 47TH ST |
Few samples shown above, the format is always same and i have tried this formula but it spills across
=RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND({1,2,3,4,5,6,7,8,9},A2,1)-1)))
How do i get the Mailing Street, have tried few other formulas as well, by looking around here, but ain't suits with the one i need.
CodePudding user response：
This is what I have tried, you may try as well,
Use any one of the either,
• Formula used in cell B2
=REPLACE(A2,1,MIN(IFERROR(FIND(CHAR(ROW($Z$48:$Z$57)),A2),""))-1,"")
And since you have tagged MS365, if you enable the Beta Channel from Insiders Option, then you may try the second one as well,
• Formula used in cell C2
=SUBSTITUTE(A2,TEXTBEFORE(A2,CHAR(ROW($Z$48:$Z$57))),"")
Two more ways, as advised by @JvdV Sir
=SUBSTITUTE(A2,TEXTBEFORE(A2,ROW($1:$10)-1),"")
Or,
=SUBSTITUTE(A2,TEXTBEFORE(A2,SEQUENCE(10)-1),"")