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.

 

[mba_110]

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))

Thanks for your answer, however the formation is coming from left to right but i need it from right to left, for example if V9 has a starting digit than formation should be from v9 back to A9, but here in this formula it's going from A9 to V9 which is opposite of my requirement, however the formula is correct and showing correct result just need to do right alignment.

Sorry to not mention that its not an English digits so, it starts from right side and ends at left side.

 

[Fluff]

I'm afraid a I don't understand what you are saying.

 

[Georgiboy]

Like this:

=LET(t,SUBSTITUTE(A1," ",""),UNIQUE(MID(t,SEQUENCE(,LEN(t),LEN(t),-1),1),1))

Or

=LET(t,SUBSTITUTE(A1," ",""),UNIQUE(MID(t,SEQUENCE(,LEN(t),1),1),1))

Both tested in RTL layout

 

[mba_110]

it will be be like this,

J 9

K9

L9

M9

N9

O9

P9

Q9

R9

S9

T9

U9

V9

ی

گ

و

ہ

ش

ر

ا

ب

ل

ک

ا

ی

ک

ب

ل

ا

ی

ک

As you can see when any digit appear repeatedly in second line it will be eliminated and moved on to next digit in first row, but it is started from right side, your formula is calculating from left side this is all the difference we have here.

for example in above first line at S9 has the digit which is already recorded in second line at V9 so the formula has to move to R9 to see new digit which is not in second line up to the point of S9, if R9 also have the digit which is recorded in second line than it continue looking for new digit by moving one column to left side.

 

[Georgiboy]

Is that in response to post 13?

 

[mba_110]

Is that in response to post 13?

Yes sir.

 

[Georgiboy]

In my post I offered 2 formulas both tested in RTL view. One formula puts the text backwards (for me) and the other forwards, did you test both?

 

[mba_110]

In my post I offered 2 formulas both tested in RTL view. One formula puts the text backwards (for me) and the other forwards, did you test both?

Its working sir for the first approach, in post no # 14 what i have showed you is different approach than post # 13.

Post No # 13 is for separating words in to digits (that is working fine, its over)
Post No # 14 is for eliminating the repeated digits in second line as i mentioned in post and done some examples for few digits, if you look at it carefully in secondly line i am taking only digits that are unique and not repeated digits, otherwise formula need to look for new digit in next column.

 

[Georgiboy]

Both of the formulae in post 13 remove duplicates from the result.

textsplit (3) (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Hello World	d	l	r	o	W	e	H
2	Hello World	H	e	l	o	W	r	d
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	=LET(t,SUBSTITUTE(A1," ",""),UNIQUE(MID(t,SEQUENCE(,LEN(t),LEN(t),-1),1),1))
B2:H2	B2	=LET(t,SUBSTITUTE(A2," ",""),UNIQUE(MID(t,SEQUENCE(,LEN(t),1),1),1))
Dynamic array formulas.

Do they both give you the wrong result?

 

[mba_110]

Both of the formulae in post 13 remove duplicates from the result.

textsplit (3) (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Hello World	d	l	r	o	W	e	H
2	Hello World	H	e	l	o	W	r	d
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	=LET(t,SUBSTITUTE(A1," ",""),UNIQUE(MID(t,SEQUENCE(,LEN(t),LEN(t),-1),1),1))
B2:H2	B2	=LET(t,SUBSTITUTE(A2," ",""),UNIQUE(MID(t,SEQUENCE(,LEN(t),1),1),1))
Dynamic array formulas.

Do they both give you the wrong result?

Its working thank you.