extract an address from multple words and a number in a cell

[excelZen]

Hello,

In cell A1=990988 barker bed golf and beyond #456545 2434 main st 98745.
(contains the ID number, business name, and address)


i want cell b1 to show (the address) 2434 main st 98745

I want cell c1 to show all the other information besides the address and the ID number before the business name.
(just the business name)


how do I make permanant formula or process, like multiple formulas to temporary trim the data for this without VBA.

Please keep in mind, the ID number is always the same amount of digits, but the business name may have different words and different spaces, and may not have a number after the business name.

Thanks.

[MarvinP]

Hi excelZen,

Can you use the "#" sign to deal with splitting up this string? See the attached.

[excelZen]

Hi Marvin,
Thanks for your reply.
Your solution of =MID(A1,FIND("#",A1)+8,100)

works, for the example above, but what happens when there is no # sign.


Also i wanted C1 to "show barker bed golf and beyond #456545"

and not the numbers in front of barker bed golf and beyohd.

is there a way to use the left, mid or right, to count the number of spaces in the cell and then use a formula.
Not all entries have a # sign , but do have multiple spaces, but not all the number of spaces are the same.

[excelZen]

EDIT: i was thinking if I removed the zipcode with a right command, all we would have left is the address, which would be :

number value, text text text. 2020 Johnson court

would there be a formula for excel to find the 1st numerical value and the end of the cell, and return the numerical value and all the text after it.

[István Hirsch]

If the data is in col A, enter this in B1 with Ctrl + Shift + Enter:

=RIGHT(A1,MATCH(1,1*(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))-9,1)=" ")*ISNUMBER(1*MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))-8,1)),0)+9)


Enter this formula in C1 (just with Enter):

=MID(SUBSTITUTE(A1," "&B1,""),8,100)

[excelZen]

Thanks, Istvan Hirsch and Marvin P and everyone that viewed this topic and tried to help.

Marvin thanks for your formula the 1st part worked and 2nd did too.
But the 2nd formula doesn't work for all my entries. Some entries have different amount of spaces and words.

I used a difference of lens from the result from cell B1 and A1 and used a left command.

THanks ALL! I will mark this as solved.