Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Digits in a string
I want to know how many digits (numbers) there are at the end of a string,
so that I may move them elsewhere. For example: A1= "Consider This Example489". This example has three digits. Because I know A1 has 3 digits (after someone explains how to compute it), then B1=Right(A1,3)=489. Thanks, Bernie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Digits in a string
Public Function numLen(rng as Range)
Dim sStr as String, i as Long sStr = Range("A1").Value for i = 1 to len(sStr) if isnumeric(Mid(sStr,i,1)) Then numlen = len(sStr)- i + 1 exit for end if Next End Function Demo'd from the immediate Window: Range("B9").Value = "Consider This Example489" ? numLen(Range("B9")) 3 ? Right(Range("B9"),numlen(Range("B9"))) 489 or are you looking for a worksheet formula solution? -- Regards, Tom Ogilvy "bw" wrote in message ... I want to know how many digits (numbers) there are at the end of a string, so that I may move them elsewhere. For example: A1= "Consider This Example489". This example has three digits. Because I know A1 has 3 digits (after someone explains how to compute it), then B1=Right(A1,3)=489. Thanks, Bernie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Digits in a string
This will extract the digits at the end
=MID(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1: A"&LEN(A1))),1))), 255,ROW(INDIRECT("A1:A"&LEN(A1))))),99) which is an array, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "bw" wrote in message ... I want to know how many digits (numbers) there are at the end of a string, so that I may move them elsewhere. For example: A1= "Consider This Example489". This example has three digits. Because I know A1 has 3 digits (after someone explains how to compute it), then B1=Right(A1,3)=489. Thanks, Bernie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Digits in a string
Thank you both, Tom and Bob.
I wanted a spreadsheet solution, but I didn't know how to do it with code either. I don't understand the spreadsheet solution as provided by Bob, but it does work. I'll have to see if I can figure out what you have done here to make this work. Thank you both again. Bernie "bw" wrote in message ... I want to know how many digits (numbers) there are at the end of a string, so that I may move them elsewhere. For example: A1= "Consider This Example489". This example has three digits. Because I know A1 has 3 digits (after someone explains how to compute it), then B1=Right(A1,3)=489. Thanks, Bernie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Digits in a string
Put simply, my formula looks at each character in turn and multiplies it by
1. This will error if is not a number, so it easily extracts the numeric values. -- HTH RP (remove nothere from the email address if mailing direct) "bw" wrote in message ... Thank you both, Tom and Bob. I wanted a spreadsheet solution, but I didn't know how to do it with code either. I don't understand the spreadsheet solution as provided by Bob, but it does work. I'll have to see if I can figure out what you have done here to make this work. Thank you both again. Bernie "bw" wrote in message ... I want to know how many digits (numbers) there are at the end of a string, so that I may move them elsewhere. For example: A1= "Consider This Example489". This example has three digits. Because I know A1 has 3 digits (after someone explains how to compute it), then B1=Right(A1,3)=489. Thanks, Bernie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Digits in a string
Thanks for the explanation Bob. It helps me to understand it somewhat, but
not enough to make a change. The formula does not work when there are embedded numbers. For example, "ABC 123 DEF 9". I only want to know how many numbers are at the END of the string...in this case 1 (the number 9). Got a fix? Thanks, Bernie "Bob Phillips" wrote in message ... Put simply, my formula looks at each character in turn and multiplies it by 1. This will error if is not a number, so it easily extracts the numeric values. -- HTH RP (remove nothere from the email address if mailing direct) "bw" wrote in message ... Thank you both, Tom and Bob. I wanted a spreadsheet solution, but I didn't know how to do it with code either. I don't understand the spreadsheet solution as provided by Bob, but it does work. I'll have to see if I can figure out what you have done here to make this work. Thank you both again. Bernie "bw" wrote in message ... I want to know how many digits (numbers) there are at the end of a string, so that I may move them elsewhere. For example: A1= "Consider This Example489". This example has three digits. Because I know A1 has 3 digits (after someone explains how to compute it), then B1=Right(A1,3)=489. Thanks, Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This cell contains a date string rep. with only 2 digits for the y | Excel Discussion (Misc queries) | |||
counting Row() digits? | Excel Discussion (Misc queries) | |||
Convert string of digits into a date | Excel Worksheet Functions | |||
Zero appears at the end of a string of 16 digits. Two zeros after | Excel Discussion (Misc queries) | |||
How do I specific digits in a long string? | Excel Discussion (Misc queries) |