Separate Letters from a cell with the space

[Sekars]

Hello Friends

I need to separate the letters as shown in light yellow cells by formula.

thanks in advance.

[Crooza]

If they are all single letters separated by a space this should work in C1 dragged across and down as far as you need
=IFERROR(IF(COLUMNS($C$1:C1)>1+LEN($A1)-LEN(SUBSTITUTE($A1," ","")),"",MID($A1,FIND("~",SUBSTITUTE($A1," ","~",COLUMNS($C$1:C1)))-1,1)),RIGHT($A1,1))

[oeldere]

with text to column and a space as seperator.

see the attached file (yellow cells)

[Green Crocodile]

You can use this formula. Drag right and down.

Please Login or Register  to view this content.

[AliGW]

with text to column and a space as seperator.

see the attached file (yellow cells)

I think it might be worth mentioning that if this option is used, the OP will need to set the destination cell in step 3 to =$C$1 for the output to appear where it is shown in the sample sheet.

[Sekars]

Hello Crooza, oeldere, Green Crocodile and AliGW

Crooza and Green Crocodile :
thanks for your formulas which is working well.

oeldere, Green Crocodile and AliGW
This procedure i know already which i mentioned in #1 excel file. But i need the formula solution.

thanks again to all thread replies.

[Green Crocodile]

I provided you with the formula. Please refer to post #4.

[oeldere]

If you know the text to column solution, please also add that in your threat. Then we don't have to offer that solution.



You're welcome. We appreciate the feedback!

Thanks for marking the question solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

[Tony Valko]

Here's another one...

Your file is set to manual calculation. Set it to automatic.

This formula entered in C1:

=TRIM(MID(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",255)),COLUMNS($C1:C1)*255,255))

Copy down as needed then across until you get a column full of blanks.

[Sekars]

Hello Green Crocodile, oeldere, Tony

Green Crocodile :

Sorry it is typo, i already saw and checked your formula which is working well, wrongly typed your name.


oeldere
Sorry added it in my excel but in thread i am not type.

Tony

thanks for your formula which is working well.

[Tony Valko]

You're welcome. Thanks for the feedback!