Split string after first numeric element

问题:

Does someone know a function in Excel that would separate this:

Tottenham Court Road 28 Apartment 8

to Tottenham Court Road 28 and Apartment 8 ?

Something like "after first number - split to another column".

回答1:

Shorter version of the formula approach:

Left side

=TRIM(LEFT(A1,FIND(" ",A1,  MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")))))

Right side

=TRIM(MID(A1,FIND(" ",A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))),LEN(A1)))

Both entered as Array Formulas (press Ctrl-Shift-Enter)

回答2:

This should work:

Left side:

=TRIM(LEFT(A1,FIND(" ",A1,MIN(IFERROR(FIND(0,A1),LEN(A1)),IFERROR(FIND(1,A1),LEN(A1)),IFERROR(FIND(2,A1),LEN(A1)),IFERROR(FIND(3,A1),LEN(A1)),IFERROR(FIND(4,A1),LEN(A1)),IFERROR(FIND(5,A1),LEN(A1)),IFERROR(FIND(6,A1),LEN(A1)),IFERROR(FIND(7,A1),LEN(A1)),IFERROR(FIND(8,A1),LEN(A1)),IFERROR(FIND(9,A1),LEN(A1))))))

Right side:

=TRIM(MID(A1,FIND(" ",A1,MIN(IFERROR(FIND(0,A1),LEN(A1)),IFERROR(FIND(1,A1),LEN(A1)),IFERROR(FIND(2,A1),LEN(A1)),IFERROR(FIND(3,A1),LEN(A1)),IFERROR(FIND(4,A1),LEN(A1)),IFERROR(FIND(5,A1),LEN(A1)),IFERROR(FIND(6,A1),LEN(A1)),IFERROR(FIND(7,A1),LEN(A1)),IFERROR(FIND(8,A1),LEN(A1)),IFERROR(FIND(9,A1),LEN(A1)))),999))

回答3:

Not tested for the assumption that the example provided may be one of many (even if all of similar format) but one way would be to copy ColumnA (assuming that is where your data is) to ColumnE, apply Text to Columns there with space as the delimiter, then in B1:

=TRIM(LEFT(A1,FIND(OFFSET(E1,,MATCH(INDEX(E1:Z1,MATCH(1,INDEX(ISNUMBER(E1:Z1)+0,0),0)),E1:Z1,0)),A1)-1))  

and in C1:

=TRIM(MID(A1,FIND(OFFSET(E1,,MATCH(INDEX(E1:Z1,MATCH(1,INDEX(ISNUMBER(E1:Z1)+0,0),0)),E1:Z1,0)),A1),LEN(A1))) 

with both copied down to suit.

回答4:

Assuming your original address is in cell A2, I suggest using a whole lot of helper columns that you'll hide after the fact. This allows you to use easy simple formulae in each column, which aids in debugging.

With that in mind, this solution runs along the same lines as that provided by Taosique, just laid out the way I prefer to do these things.

---

I would use column headers of:

• Address, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, First Number, Space, Output 1, Output 2

i.e. important columns B through K contain the all the digits. Each of these columns will be used to search for each of the digits 0 through 9.

---

Use this formula in B2, copy through to K2. It will give you the offset of any found digit:

=IFERROR(search(B$1,$A2),len($A2))

L2 finds the first digit by taking the minimum of the previous columns:

=min(B2:K2)

M2 finds the first space after this number, giving you the dividing index that you want:

=search(" ",$A2,L2)

---

N2 and O2 simply take the LEFT and RIGHT of your string, based on the index found above:

=left($A2,M2-1)

=right($A2,len($A2)-M2)

---

Simple formulas spread out over the columns - now hide the columns and you are all set.

If your formula for splitting the string changes in the future, you can easily adapt the simple formulas.

回答5:

Formula is possible, but not really feasible here. Let's assume your full text is in cell A1:

Tottenham Court Road 28 Apartment 8

Then in cell B1 we will determine index of first space after first number:

=SEARCH(" ",A1,IFERROR(SEARCH("0",A1),IFERROR(SEARCH("1",A1),IFERROR(SEARCH("2",A1),IFERROR(SEARCH("3",A1),IFERROR(SEARCH("4",A1),IFERROR(SEARCH("5",A1),IFERROR(SEARCH("6",A1),IFERROR(SEARCH("7",A1),IFERROR(SEARCH("8",A1),IFERROR(SEARCH("9",A1),1)))))))))))

Knowing this, in cell C1 we have the first part of expression:

=LEFT(A1,B1-1)

In cell D1 we have the second part:

=MID(A1,B1+1,LEN(A1))