Flora Edwards
asked on
formula to add space between alpha numeric string
hello
i need help with the formula.
i have mixed alpha numberic string in many cells in column A and i want a formula in column B that adds space between alpha and numbers either way. for example if an text with number is attached like this James54125 Nick5411 then in ciolumn B result James 54125 Nick 5411 or the other way 5412211James 74555555Nicolay result in column B 5412211 James 74555555 Nicolay
please see attached file. i found the formula =TRIM(LEFT(A2, MATCH(TRUE, MID(A2, {1,2,3,4,5,6,7}, 1)>"9", 0) - 1) & " " & MID(A2, MATCH(TRUE, MID(A2, {1,2,3,4,5,6,7}, 1)>"9", 0), 100) )
but it does not work, please see attached file.
Book2.xlsx
i need help with the formula.
i have mixed alpha numberic string in many cells in column A and i want a formula in column B that adds space between alpha and numbers either way. for example if an text with number is attached like this James54125 Nick5411 then in ciolumn B result James 54125 Nick 5411 or the other way 5412211James 74555555Nicolay result in column B 5412211 James 74555555 Nicolay
please see attached file. i found the formula =TRIM(LEFT(A2, MATCH(TRUE, MID(A2, {1,2,3,4,5,6,7}, 1)>"9", 0) - 1) & " " & MID(A2, MATCH(TRUE, MID(A2, {1,2,3,4,5,6,7}, 1)>"9", 0), 100) )
but it does not work, please see attached file.
Book2.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you are brilliant