Return Value embedded at the end of Text String

[tichota]

I'm having trouble with the forumula to return the value at the end of text string. Below is a sample of the list I'm working off of. Want to retrn the value at the end (everything right of the last period). Any thoughts? Thanks


<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=512 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 384pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=512 colSpan=8 height=17>400000210 ORIGINAL TOWN S 55' LT 4 BLK 5 ..................................... 977.66</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400000377 ORIGINAL TOWN LT 7 BLK 6 ........................................... 963.88</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400000652 ORIGINAL TOWN LT 1 BLK 9 ........................................... 1,072.46</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400000857 ORIGINAL TOWN LT 6 BLK 11 .......................................... 192.64</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400001314 ORIGINAL TOWN LT 2 XC S 49' OF W 33.6' BLK 17 ...................... 1,146.56</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400001454 ORIGINAL TOWN LT 4 BLK 18 .......................................... 628.71</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400001489 ORIGINAL TOWN S 1/2 LT 1 & S 1/2 LT 2 BLK 19 ....................... 1,762.96</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400001748 ORIGINAL TOWN E 32.5' LT 7 BLK 21 .................................. 283.58</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400001896 ORIGINAL TOWN LOT 3 BLK 27 ......................................... 634.33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17>400001934 ORIGINAL TOWN S 92' LOT 5 BLK 27 ................................... 509.54</TD></TR></TBODY></TABLE>

 

[jim may]

Excel Workbook
	A	B
1	My Imported Data	My UDF
2	400000210 ORIGINAL TOWN S 55' LT 4 BLK 5 ..................................... 977.66	977.66
3	400000377 ORIGINAL TOWN LT 7 BLK 6 ........................................... 963.88	963.88
4	400000652 ORIGINAL TOWN LT 1 BLK 9 ........................................... 1,072.46	1072.46
5	400000857 ORIGINAL TOWN LT 6 BLK 11 .......................................... 192.64	192.64
6	400001314 ORIGINAL TOWN LT 2 XC S 49' OF W 33.6' BLK 17 ...................... 1,146.56	1146.56
7	400001454 ORIGINAL TOWN LT 4 BLK 18 .......................................... 628.71	628.71
8	400001489 ORIGINAL TOWN S 1/2 LT 1 & S 1/2 LT 2 BLK 19 ....................... 1,762.96	1762.96
9	400001748 ORIGINAL TOWN E 32.5' LT 7 BLK 21 .................................. 283.58	283.58
10	400001896 ORIGINAL TOWN LOT 3 BLK 27 ......................................... 634.33	634.33
11	400001934 ORIGINAL TOWN S 92' LOT 5 BLK 27 ................................... 509.54	509.54
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=GetLastNumber(A2)

Paste into a Standard Module:

Function GetLastNumber(R As Range) As Double
    NumPos = InStrRev(R, "...") + 4
GetLastNumber = Mid(R, NumPos) + 0
End Function

 

[Gerald Higgins]

Or as a basic formula

=MID(A1,FIND(". ",A1,1)+2,255)

Add +0 to the end if you want to return the result as numeric data.

This works for all the examples given, although if you have other data that contains the string ". " somewhere else other than immediately before the last number, it won't work properly.

 

[tichota]

Works great. Thanks

 

[Comfy]

=right(a1,len(a1)-find("@",substitute(a1,".","@",len(a1)-1-len(substitute(a1,".","")))))