Text formula to separate the string text.

[sachar]

Dear troubleshooter,

I am unable to understand the lengthy formula, which has been applied in attachment file column no "Q", who can help me to explore in details to understand the logic?

"=TRIM(LEFT(RIGHT(SUBSTITUTE(B2,",",REPT(" ",LEN(B2))),LEN(B2)),LEN(B2)))&" "&MID(TRIM(LEFT(SUBSTITUTE(B2,",",REPT(" ",LEN(B2))),LEN(B2))),2,20)

 

[eibi]

Sachar,

This formula uses three segments to create a new string:

TRIM(LEFT(RIGHT(SUBSTITUTE(B3,",",REPT(" ",LEN(B3))),LEN(B3)),LEN(B3)))

&

" "

&

MID(TRIM(LEFT(SUBSTITUTE(B3,",",REPT(" ",LEN(B3))),LEN(B3))),2,20)

In both Segment 1 and Segment 3, the formula counts the length of the original string (LEN(B3)) and uses this string length several times.

First, the SUBSTITUTE() function replaces each single space character (that is, each " ") with a set of spaces equal to the length of the whole original string.

Then, essentially, the RIGHT() or LEFT() function extracts a segment of text from the beginning or end of the string that is equal to the string length. The result is either the first word or the last word of the original string, along with a "buffer" of spaces that were inserted in the previous step.

Finally, the TRIM() function chops off the extra spaces -- and all you are left with is the first word or the last word of the original string.

Segment 3 has an additional MID() function to cut off the first character (usually an asterisk in your data set)...this is why on Line 3, you are losing the first character in Ms. Close.

Hope this helps...

All best.

 

[prasaddn]

Hi,

Also you may be interested to tweak a bit in formula to drop the first char of string only if it *, because I see few cases where the one letter of the name is missing.

regards,
Prasad DN

 

[eibi]

Prasad,

It looks to me like the LEFT(RIGHT()) construction in the first segment is redundant...

Could you can eliminate the LEFT() function and have the same result?

=TRIM(RIGHT(SUBSTITUTE(B3,",",REPT(" ",LEN(B3))),LEN(B3)))&" "&MID(TRIM(LEFT(SUBSTITUTE(B3,",",REPT(" ",LEN(B3))),LEN(B3))),2,20)

I don't see a difference.