find cell then print right most text

luckky300

New Member
Joined
Jul 17, 2010
Messages
3
The following appears in varying locations (rarely same row or column) in my worksheets:

"Period: December, 2000" The month and year will also vary"

I just need to find this text string then have the text "2000" show in the cell.

I know the Right command is involved but I am stuck on determining variables.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
If years are always four digits (they usually are)

=RIGHT(A1,4)

will work
or if there's always 2 spaces in the cell

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))

will work
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Welcome to the Board...

Could you mock up a simple example for us to work with?

Matty
 

luckky300

New Member
Joined
Jul 17, 2010
Messages
3
<TABLE style="WIDTH: 539pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=719><COLGROUP><COL style="WIDTH: 539pt; mso-width-source: userset; mso-width-alt: 32877" width=719><TBODY><TR style="HEIGHT: 12.6pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 539pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl58 height=17 width=719>Each of these is what I am searching for after the conversion. Each of these appears in a different worksheet.

Tax Period: December, 2006 (cell d16 after conversion)

<TABLE style="WIDTH: 539pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=719><COLGROUP><COL style="WIDTH: 539pt; mso-width-source: userset; mso-width-alt: 32877" width=719><TBODY><TR style="HEIGHT: 12.6pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 539pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl60 height=17 width=719>Tax Period: December, 1996 (cell b14 when converted)

<TABLE style="WIDTH: 539pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=719><COLGROUP><COL style="WIDTH: 539pt; mso-width-source: userset; mso-width-alt: 32877" width=719><TBODY><TR style="HEIGHT: 12.6pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 539pt; HEIGHT: 12.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl60 height=17 width=719>Tax Period: December, 2009 (cell f20 when converted)

I am converting files from HTML to Excel through a converter. There is no consistency to which cell the "tax period....." string shows up in. I do have a macro in the process that will shift everything to the left and delete empty cells. After this occurs this string will appear in Column A (First column) but the row will vary. The only thing that changes in the test string is the year. The month of December always exists as it is an annual report.

The posted code above works but only if I already know the location of the text string.

I hope this is proper format and explanation and I really appreciate the help.
</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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
Top