Extract Data from the middle of a cell

[westend9876]

Hi. I attached a spreadsheet with addresses. I need to extract the house number and the street name from each cell and put them each in their own cell. Columns G and H are the columns with the new data. I provided an example in the first five rows of what data should be extracted for each column.

 

[VDS]

@Wesetend,

For taking House Number from column B, you can use =LEFT (B7,5). & stretch to downwards. Because most of the numeric are in 5 characters and adjust spaces wherever required.


VDS

 

[westend9876]

Hi. That's a possible s

@Wesetend,

For taking House Number from column B, you can use =LEFT (B7,5). & stretch to downwards. Because most of the numeric are in 5 characters and adjust spaces wherever required.


VDS

Hi. That's a possible solution. However, that does not solve the problem of extracting the text from the center of the cell. Any suggestions for that? Thanks.

 

[shrivallabha]

Names and addresses are difficult to play with unless there is some solid rule / criteria that can be used.

Try following formula as in G2 (CTRL+SHIFT+ENTER) and see if it works for you:
=LEFT(B2,MIN(SEARCH(CHAR(ROW($A$65:$A$90)),B2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1)
copy down

Or alternatively it can be entered as non-array way (basically same formula except all characters typed) and it will be bit bigger but will be faster.
=LEFT(B2,MIN(SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},B2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1)
copy down

And then in H2:
=MID(B2,LEN(G2)+1,99)
copy down