How to Treat Comma Delim Cells Like an Array?

[arh428]

Hello all,

Currently I have a column of cells that contain numbers like this:

"1,13,19"
"2,14,19,45,50"
"1,12,21,31"
"14"
"11,13,21,30"
...

and also a column of ID numbers.

I am hoping to convert the individual values to characters so that I can combine it with an ID and perform a countif. (for example, I want a count of "1"s in the columns, without counting 19 or an ID of 123456789).

I am wondering if there is any way to modify all of the numbers in a cell, and output the transformed delimited list. e.g

"1,3,7,19" ->> "a,c,g,s"

Ideally, this would be accomplished through formulas. If it is in VBA, then I need a VBA formula, since the list will be constantly updated, and often not by me.

Thanks for the help!

arh428

 

[Scott Huish]

Your first question should work with this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(",1,",","&A1:A5&",")))

just change the search value to have a comma in front of and behind the search value.

 

[Scott Huish]

Does this work for your second question?

Function ConvertNumbersToLetters(r As String) As String
Dim c, t, s As String
c = Split(r, ",")
For Each t In c
    If t < 27 Then s = s & Chr(96 + t) & ","
Next
ConvertNumbersToLetters = Left(s, Len(s) - 1)
End Function

 

[arh428]

Does this work for your second question?

Function ConvertNumbersToLetters(r As String) As String
Dim c, t, s As String
c = Split(r, ",")
For Each t In c
    If t < 27 Then s = s & Chr(96 + t) & ","
Next
ConvertNumbersToLetters = Left(s, Len(s) - 1)
End Function

Hi Scott,

This code only worked up to the end of the alphabet for me. However, I think a modified version of your above suggestion will work. I hadn't thought of simply adding a comma to the beginning and end of the cell! I think I'll use a match function with wild cards instead of search since I'm looking for both the number and an ID in the same cell.

Thanks for the help!

arh428

 

[Rick Rothstein]

Hello all,

Currently I have a column of cells that contain numbers like this:

"1,13,19"
"2,14,19,45,50"
"1,12,21,31"
"14"
"11,13,21,30"
...

I am wondering if there is any way to modify all of the numbers in a cell, and output the transformed delimited list. e.g

"1,3,7,19" ->> "a,c,g,s"

What letters should the 45 and 50 be converted to?

 

[Scott Huish]

and the 30 and 31?