Pick the required text from cell

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
221
Hi Experts,

I want to get specific text from the Column A to B like follows, please help with formula

SPVAR JAN 19SPVAR
IFG JAN 19IFG
R1 Adjustment JAN 19R1 Adjustment
Stock Adjustment JAN 19Stock Adjustment
IFG FEB 19IFG
SPVAR FEB 19SPVAR
Stock Adjustment FEB 19
Stock Adjustment
IFG MAR 19IFG

<tbody>
</tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Re: Pick the requierd text from cell

If your data always has the format: "specific text" MMM YY
as seen in your examples then the formula in B1 copied down will work.
Excel Workbook
AB
1SPVAR JAN 19SPVAR
2IFG JAN 19IFG
3R1 Adjustment JAN 19R1 Adjustment
4Stock Adjustment JAN 19Stock Adjustment
5IFG FEB 19IFG
6SPVAR FEB 19SPVAR
7Stock Adjustment FEB 19Stock Adjustment
8IFG MAR 19IFG
Sheet2
 
Upvote 0
Re: Pick the requierd text from cell

If your data always has the format: "specific text" MMM YY
as seen in your examples then the formula in B1 copied down will work.

Spreadsheet Formulas
CellFormula
B1=IFERROR(MID(A1,1,LEN(A1)-7),"")

<tbody>
</tbody>

<tbody>
</tbody>
Why not use LEFT instead of MID?

=IFERROR(LEFT(A1,LEN(A1)-7),"")

Also, if the day number is not always two digits (leading zero for single digit days), then this should work (assuming 3-character months are always used)...

=IFERROR(TRIM(LEFT(A1,LEN(A1)-6)),"")
 
Last edited:
Upvote 0
Re: Pick the requierd text from cell

Why not use LEFT instead of MID?

=IFERROR(LEFT(A1,LEN(A1)-7),"")

Also, if the day number is not always two digits (leading zero for single digit days), then this should work (assuming 3-character months are always used)...

=IFERROR(TRIM(LEFT(A1,LEN(A1)-6)),"")
What's the advantage of LEFT over MID?
 
Last edited:
Upvote 0
Re: Pick the requierd text from cell

What's the advantage of LEFT over MID?
Mainly, for me, self-documentation. If I see MID, I am expecting characters inside the text to be selected... if I see LEFT, I know beginning characters are being selected... and if I see RIGHT, I know ending characters are being selected.
 
Upvote 0
Re: Pick the requierd text from cell

Its working but included months as well but I want results without months only IFG, SPVAR, Stock Adjustment & R1 Adjustment

Result comes after formula = IFG JANUARY
I need = IFG
 
Upvote 0
Re: Pick the requierd text from cell

only 3 words I want from array, do I put these somewhere and find in array ?

IFG
SPVAR
R1
 
Upvote 0
Re: Pick the requierd text from cell

I have applied formula
=IFERROR(MID(A1,1,LEN(A1)-7),"") and getting the result in column B where as I need result as shown in column C Please help.

IFG JANUARY 2019IFG JANUAIFG
SPVAR JANUARY 2019.SPVAR JANUARSPVAR
IFG JANUARY 2019IFG JANUAIFG
SPVAR JANUARY 2019.SPVAR JANUARSPVAR
IFG FEBRUARY 2019IFG FEBRUAIFG
SPVAR FEBRUARY 2019.SPVAR FEBRUARSPVAR
R1 Adjustment, February 2019.R1 Adjustment, FebruarR1 Adjustment
IFG MAR 19IFGIFG

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Re: Pick the requierd text from cell

I have applied formula... and getting the result in column B
That is because all of the examples posted in Message#1 were constructed the same, but those examples were not representative of all of your actual data.

Give this formula a try...

=SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),200)))-1),",","")
 
Upvote 0
Re: Pick the requierd text from cell

WOW !! Is this Excel or some kind of New Science !!,

Very good Thanks Rick
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top