CODEWITHSUNDEEP
excelexcel-formula
Hanish Kiran
Hanish Kiran

Reputation: 15

How Do I extract the 5th and 6th rightmost characters from excel

I want to extract the 5th and 6th rightmost characters from excel. How do I do that?

I have tried to use the mid function in excel, but it takes the reference from the first most cell

I want to extract Numbers 30, 29, 20 and 28 respectively 

Days
2 months, 30 days
12 months, 29 days
112 months, 20 days
3 months, 28 days

Expected results: 30 29 20 28

Upvotes: 0

Views: 5682

Answers (2)

Terry W
Terry W

Reputation: 3257

You can also try the following formula given that you want to return the third word within the text string. Drag it down to apply across.

=FILTERXML("<data><a>"&SUBSTITUTE(A1," ","</a><a>")&"</a></data>","/data/a[3]")

Solution

For the logic behind this formula you may give a read to this article: Extract Words with FILTERXML.

Upvotes: 0

henrywongkk
henrywongkk

Reputation: 1918

Assume the date is in cell A2, you can make use of LEFT() and RIGHT()

=LEFT(RIGHT(A2,7), 2)

Or using LEN() to get the length of string and use MID() to start with position = Length - 6

=MID(A2, LEN(A2)-6, 2)

Upvotes: 1

Related Questions