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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
Welcome to the Board...

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

Matty
 
Upvote 0
<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>
 
Upvote 0

Forum statistics

Threads
1,212,151
Messages
6,106,259
Members
448,009
Latest member
wbarkwell

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