Word to Digits in Separate Cells one after another

[mba_110]

I am trying for formula in excel to split the word in to digits on each separate cell in same row, as described below.

B10 = Mirza Bahadur Ali

Result Required: C10 = M, D10 = i, E10 = r, F10 =z, G10 = a, <No Space> H10 = B, I10 =a , J10 = h, K10 = a,L10 =d , M10 = u,N10 = r, <No Space> O10 = A, P10 = l, Q10 = i

Many thanks in advance for your kind help.

 

[Georgiboy]

What version of excel do you use?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 2021 or 365 how about

=LET(d,SUBSTITUTE(A2," ",""),MID(d,SEQUENCE(,LEN(d)),1))

 

[Georgiboy]

New and old way to do it:

TEXTS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Mirza Bahadur Ali	M	i	r	z	a	B	a	h	a	d	u	r	A	l	i
2	Mirza Bahadur Ali	M	i	r	z	a	B	a	h	a	d	u	r	A	l	i
Sheet1

Cell Formulas
Range	Formula
B1:P1	B1	=LET(t,SUBSTITUTE(A1," ",""),MID(t,SEQUENCE(,LEN(t),1),1))
B2:P2	B2	=MID(SUBSTITUTE($A2," ",""),COLUMN()-1,1)
Dynamic array formulas.

 

[mba_110]

I am sorry that i have missed to mention the version its office 365, also i need to mention that i am using right to left alignment (Text direction) so the starting should be from right and not left.

 

[Fluff]

i have missed to mention the version its office 365

Please update your account to show this as requested in post#3

i am using right to left alignment

In that case try

=LET(d,SUBSTITUTE(A2," ",""),MID(d,SEQUENCE(,LEN(d),LEN(d),-1),1))

 

[mba_110]

I have selected the office 365 check box for my excel usage.
But this message is pop up every time. " Due to the large number of posts being made using AI tools (e.g. chatGPT, BARD, GPT4) that do not work, it has been decided to ban the use of all AI sources for answering questions."

However, if its not saved please let me know i will do that again, secondly i need to change it slightly in above formula actually its different formula than above but we will use the same data to analyse it.

B10 = Mirza Bahadur Ali
Result Required: M i r z a B h d u l

That means I need once any word appear first time the formula has to take it in separate cell as above but when the same word appearing more than once then formula has to ignore it and move on to next word it doesn't matter how many times it appear (After First) it need to be eliminated in condition.

 

[Fluff]

Did the formula in post#6 do what you initially asked for?

 

[mba_110]

Yes it did serve the requirement and i upvoted your reply for excellent answer.

Post no #7 is the slightly different condition based on same data inputs.

 

[Fluff]

Yes it did serve the requirement and i upvoted your reply for excellent answer.

It would still help if you replied to the thread saying it worked.

How about

=LET(d,SUBSTITUTE(B10," ",""),UNIQUE(MID(d,SEQUENCE(,LEN(d),LEN(d),-1),1),1))