Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Iterating through string in a cell
I have an application where i would like to iterate through a cell and separate integers from text values. I want to take the text values and find out if they are all the same. In addition, I want to ignore the text value "X"... For example: 1bb should just return "b" - since it is all b's and numbers. x2uu should return "u" - it meets the same criteria as above and x should be ignored. 2 or x2 would return something like "numeric" 2uubb would return something like "multiples" Does anyone have any ideas if this is possible in Excel? I would prefer a formula, but a macro would be ok too. Please help. Thanks so much! -- joeboe ------------------------------------------------------------------------ joeboe's Profile: http://www.excelforum.com/member.php...o&userid=27900 View this thread: http://www.excelforum.com/showthread...hreadid=474004 |
#2
|
|||
|
|||
Hi Joeboe,
I can't imagine such a formula, but here you are a function: Public Function joeboe(inputstring) As String Dim i As Long Dim ByteStr As String, ResultStr As String, FirstLetter As String Dim NumByte As Boolean ResultStr = "" FirstLetter = "" For i = 1 To Len(inputstring) ByteStr = Mid(inputstring, i, 1) NumByte = IsNumeric(ByteStr) If ByteStr < "x" Then If FirstLetter = "" And Not IsNumeric(ByteStr) Then FirstLetter = ByteStr ResultStr = ByteStr Else If ByteStr = FirstLetter Then ResultStr = ByteStr Else If Not IsNumeric(ByteStr) Then ResultStr = "Multiples" Exit For End If End If End If End If Next i If ResultStr = "" Then If NumByte Then ResultStr = "Numeric" End If joeboe = ResultStr End Function Sorry if you find cases which the function returns wrong answer for. Regards, Stefi €˛joeboe€¯ ezt Ć*rta: I have an application where i would like to iterate through a cell and separate integers from text values. I want to take the text values and find out if they are all the same. In addition, I want to ignore the text value "X"... For example: 1bb should just return "b" - since it is all b's and numbers. x2uu should return "u" - it meets the same criteria as above and x should be ignored. 2 or x2 would return something like "numeric" 2uubb would return something like "multiples" Does anyone have any ideas if this is possible in Excel? I would prefer a formula, but a macro would be ok too. Please help. Thanks so much! -- joeboe ------------------------------------------------------------------------ joeboe's Profile: http://www.excelforum.com/member.php...o&userid=27900 View this thread: http://www.excelforum.com/showthread...hreadid=474004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert text string into a cell reference | Excel Discussion (Misc queries) | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
cell color index comparison | New Users to Excel | |||
Numeric cell but shows as string? | Excel Worksheet Functions | |||
Need to find the right-most cell value in a string of cells | Excel Worksheet Functions |