Pick the required text from cell

[Salamullah]

Hi Experts,

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]SPVAR JAN 19[/TD]
[TD]SPVAR[/TD]
[/TR]
[TR]
[TD]IFG JAN 19[/TD]
[TD]IFG[/TD]
[/TR]
[TR]
[TD]R1 Adjustment JAN 19[/TD]
[TD]R1 Adjustment[/TD]
[/TR]
[TR]
[TD]Stock Adjustment JAN 19[/TD]
[TD]Stock Adjustment[/TD]
[/TR]
[TR]
[TD]IFG FEB 19[/TD]
[TD]IFG[/TD]
[/TR]
[TR]
[TD]SPVAR FEB 19[/TD]
[TD]SPVAR[/TD]
[/TR]
[TR]
[TD]Stock Adjustment FEB 19
[/TD]
[TD]Stock Adjustment[/TD]
[/TR]
[TR]
[TD]IFG MAR 19[/TD]
[TD]IFG[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

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
	A	B
1	SPVAR JAN 19	SPVAR
2	IFG JAN 19	IFG
3	R1 Adjustment JAN 19	R1 Adjustment
4	Stock Adjustment JAN 19	Stock Adjustment
5	IFG FEB 19	IFG
6	SPVAR FEB 19	SPVAR
7	Stock Adjustment FEB 19	Stock Adjustment
8	IFG MAR 19	IFG
Sheet2

 

[Rick Rothstein]

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

Cell Formula 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)),"")

 

[JoeMo]

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?

 

[Rick Rothstein]

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.

 

[Salamullah]

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

 

[Salamullah]

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

 

[Salamullah]

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.​

[TABLE="width: 554"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="align: left"]IFG JANUARY 2019[/TD]
[TD="align: left"]IFG JANUA[/TD]
[TD="align: left"]IFG[/TD]
[/TR]
[TR]
[TD="align: left"]SPVAR JANUARY 2019.[/TD]
[TD="align: left"]SPVAR JANUAR[/TD]
[TD="align: left"]SPVAR[/TD]
[/TR]
[TR]
[TD="align: left"]IFG JANUARY 2019[/TD]
[TD="align: left"]IFG JANUA[/TD]
[TD="align: left"]IFG[/TD]
[/TR]
[TR]
[TD="align: left"]SPVAR JANUARY 2019.[/TD]
[TD="align: left"]SPVAR JANUAR[/TD]
[TD="align: left"]SPVAR[/TD]
[/TR]
[TR]
[TD="align: left"]IFG FEBRUARY 2019[/TD]
[TD="align: left"]IFG FEBRUA[/TD]
[TD="align: left"]IFG[/TD]
[/TR]
[TR]
[TD="align: left"]SPVAR FEBRUARY 2019.[/TD]
[TD="align: left"]SPVAR FEBRUAR[/TD]
[TD="align: left"]SPVAR[/TD]
[/TR]
[TR]
[TD="align: left"]R1 Adjustment, February 2019.[/TD]
[TD="align: left"]R1 Adjustment, Februar[/TD]
[TD="align: left"]R1 Adjustment[/TD]
[/TR]
[TR]
[TD="align: left"]IFG MAR 19[/TD]
[TD="align: left"]IFG[/TD]
[TD="align: left"]IFG[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

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

 

[Salamullah]

Re: Pick the requierd text from cell

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

Very good Thanks Rick