Forum Discussion
Solved
Looking for a Formula
Respected Sir/Madam I want to extract first name, middle name, and last name with a space. But the main problem is there is no delimiter in my data. Only delimiter can be the uppercase word. My ...
In straightforward legacy style
=TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1, "A"," A"), "B"," B"), "C"," C"), "D"," D"), "E"," E"), "F"," F"), "G"," G"), "H"," H"), "I"," I"), "J"," J"), "K"," K"), "L"," L"), "M"," M"), "N"," N"), "O"," O"), "P"," P"), "Q"," Q"), "R"," R"), "S"," S"), "T"," T"), "U"," U"), "V"," V"), "W"," W"), "X"," X"), "Y"," Y"), "Z"," Z") )
In straightforward legacy style
=TRIM(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A1,
"A"," A"),
"B"," B"),
"C"," C"),
"D"," D"),
"E"," E"),
"F"," F"),
"G"," G"),
"H"," H"),
"I"," I"),
"J"," J"),
"K"," K"),
"L"," L"),
"M"," M"),
"N"," N"),
"O"," O"),
"P"," P"),
"Q"," Q"),
"R"," R"),
"S"," S"),
"T"," T"),
"U"," U"),
"V"," V"),
"W"," W"),
"X"," X"),
"Y"," Y"),
"Z"," Z")
)- Thank you very much, Sir.
Best approach.Rohit_Sonwane , you are welcome
