Extract different strings from a cell?

[cpliu]

If I have "100BDDDABAABD" in a cell and I'd like to extract the last
10 characters and put each character into a different cells. Then, the
first rest of characters (first 3 in this case) into another cell.

How do I do that in Excel?

Thanks,

 

[Ragdyer]

Since you said "first 3 in this case", I'll assume that the number of
characters will vary in each cell.

With data in A1, enter this in B1, and copy across to K1:

=MID(RIGHT($A1,10),COLUMNS($A:A),1)

Then, in L1, enter this for the remaining characters:

=LEFT(A1,LEN(A1)-10)

 

[Bernard Liengme]

With "100BDDDABAABD" in A1,if you want 100 in A1, "B" in B1, "D" in C1 etc
then use Data | Text to Column; specify Fixed and click in the window to add
line fro the break points

But you may want the data in other places. so here is another way
With the in A1
this =MID(A1,4,1) will give the first B
this =MID(A1,5,1) will give the first D
etc
this =LEFT(A1,3) will give 100 as text while =--LEFT(A1,3) will give 100 as
a number (that is - followed by - ; it is double negation)

To save time: suppose you want the first "B" in F1
In F1 enter =MID($A$1,COLUMN()-2,1) and copy this by dragging across the row
to get all 10 characters.
(F is column number 6 and we want the 4th character so we subtract 2 from
F's column)
best wishes

 

[DILipandey]

Hi,
Supposing you have the data in cell A1, use following formula in B1 and drag
it to right side to cover 10 cells:-
=MID($A$1,COUNTA($A$1:A$1)+3,1)

Now, if you want first three characters in a single cell only, then use
following in b2:-
=LEFT(A1,3)
And, if you want first three characters in different cells, then use
following in b3 and drag it to right side to cover 3 cells:-
=MID($A$1,COUNTA($A$1:A$1),1)

--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[liu]

Thank you all for the great help. It works now.

 

[Ragdyer]

Appreciate the feed-back.