• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Parse a string and replace each char with another one

kalbwa

New Member
Hello guys,
I need help with completing the following task. My input field will have up to 9 alphabetic characters 'a b c d e f g h i' (but could be more or less). My output cell length is 9 chars but the input cell can be longer as there's a space between each letter. the input cell can be blank or one char 'a' or two 'a b' or three 'c f g', ...
I need a formula to replace each char by 1 and fill the rest with 0. The 1 should be placed at the code value index of each letter. For instance if I have 'a c', I should have 1 in the first and third positions '101000000' since a and c are first and third value of the alphabet respectively; 'b d' would return '010100000' as b and d are 2nd and 4th chars of the alphabet.
My file should look like this:
Input Output
a c 101000000
c d e 001110000
a h 100000010
b i 010000001
c f h 001001010
d i 000100001
b d 010100000

Thanks
 
Hello guys,
I need help with completing the following task. My input field will have up to 9 alphabetic characters 'a b c d e f g h i' (but could be more or less). My output cell length is 9 chars but the input cell can be longer as there's a space between each letter. the input cell can be blank or one char 'a' or two 'a b' or three 'c f g', ...
I need a formula to replace each char by 1 and fill the rest with 0. The 1 should be placed at the code value index of each letter. For instance if I have 'a c', I should have 1 in the first and third positions '101000000' since a and c are first and third value of the alphabet respectively; 'b d' would return '010100000' as b and d are 2nd and 4th chars of the alphabet.
My file should look like this:
Input Output
a c 101000000
c d e 001110000
a h 100000010
b i 010000001
c f h 001001010
d i 000100001
b d 010100000

Thanks
Can you please upload a sample excel sheet with manual output format. Its little confusing for me to understand.
 
Hi,

Try below formula:

In A1 is your text:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",0),"a",1),"b",1),"c",1),"d",1),"e",1),"f",1),"g",1),"h",1),"I",1)&REPT(0,9-LEN(A1))

Regards,
 
Hi SM,

I am big fan of yours & as always like your answer.

I test the file & found that if input value is "b d" the output is "101000000". Correct me if I have understand something else because what I understood is OP wants answer to be 010100000.

Regards,
AM:)
 
@ashish mehra

What I understand from OP Q. is that each alphabet should be replaced with a 1 and rest blank spaces and length to make 9 should be 0.

Let OP clear this thing.

Regards,
 
Hi SM,

I am big fan of yours & as always like your answer.

I test the file & found that if input value is "b d" the output is "101000000". Correct me if I have understand something else because what I understood is OP wants answer to be 010100000.

Regards,
AM:)

Hi and thank you for your reply. for 'b d' I was expecting 010100000. I want each letter to be replaced by 1 at respective alphabetic index position and each other position in that string should be replaced by 0. In this case "b d", the first position for a is replaced by 0, the second pos needs a 1 to replace b, third pos 0 (no c), 4th pos needs a 1 to replace d and the rest fill with 0
 

Attachments

  • SampleFile.xlsx
    8.9 KB · Views: 5
@kalbwa

See the file. It uses helper cell. Green cell is the output cell.

Regards,
Hi SM,

Thank you very much for this solution. I tried it and it works in this case but it could be problematic for me. I have to apply this to multiple columns and some of the columns may have different lengths, my largest column size is 24, which means I'll have values from a to x. If I implemented it this way I will end up with a very large and cumbersome spreadsheet, which may look a bit scary to the end users. I was thinking of using multiple sheets where I would do the conversion for each column on one separate sheet and present the final solution on one summary sheet.
Thank you.
Kal
 
Maybe you can use REPLACE function like this assuming A1 cell contains input. Nothing pretty, plain sledgehammer approach.

=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE("000000000",1,1,IF(ISNUMBER(SEARCH("a",A1)),1,0)),2,1,IF(ISNUMBER(SEARCH("b",A1)),1,0)),3,1,IF(ISNUMBER(SEARCH("c",A1)),1,0)),4,1,IF(ISNUMBER(SEARCH("d",A1)),1,0)),5,1,IF(ISNUMBER(SEARCH("e",A1)),1,0)),6,1,IF(ISNUMBER(SEARCH("f",A1)),1,0)),7,1,IF(ISNUMBER(SEARCH("g",A1)),1,0)),8,1,IF(ISNUMBER(SEARCH("h",A1)),1,0)),9,1,IF(ISNUMBER(SEARCH("i",A1)),1,0))

Edit: It could be written this way also in this case since answers are 0 & 1
=CONCATENATE(--ISNUMBER(SEARCH("a",A1)),--ISNUMBER(SEARCH("b",A1)),--ISNUMBER(SEARCH("c",A1)),--ISNUMBER(SEARCH("d",A1)),--ISNUMBER(SEARCH("e",A1)),--ISNUMBER(SEARCH("f",A1)),--ISNUMBER(SEARCH("g",A1)),--ISNUMBER(SEARCH("h",A1)),--ISNUMBER(SEARCH("i",A1)))
 
Last edited:
Hi Kal ,

Use the following VBA , which is quite simple and straightforward :
Code:
Public Function Parse_and_Replace(InputCell As Range)
          Const ALPHABET = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z"
          Const OUTPUTLENGTH = 9
          Dim outputstring As String
          Dim Alpha_Array As Variant
         
          previndex = 0
          Alpha_Array = Split(ALPHABET, ",")
          For i = 1 To Len(InputCell)
              Character = Mid(InputCell.Value, i, 1)
              If Character <> " " Then
                  For j = previndex To UBound(Alpha_Array)
                      currchar = Alpha_Array(j)
                      If currchar = Character Then
                        outputstring = outputstring & "1"
                        Exit For
                      Else
                        outputstring = outputstring & "0"
                      End If
                  Next
                  previndex = j + 1
              End If
          Next
          Parse_and_Replace = Left(outputstring & String(UBound(Alpha_Array) - Len(InputCell), "0"), OUTPUTLENGTH)
End Function
This is a UDF , and in the worksheet cell , you would have :

=Parse_and_Replace(A2)

Copy this down.

if you later on wish to extend this to 24 characters , change the constant OUTPUTLENGTH from 9 to 24.

Narayan
 
Nice one Narayan. Maybe the outputlength can be made as optional argument (default being 9) in the function so no need to edit VBA.

Here's one more function which seems to be working as well.
=RIGHT("00000000"&SUM(MMULT(--ISNUMBER(SEARCH({"a","b","c","d","e","f","g","h","I"},A2)),{100000000;10000000;1000000;100000;10000;1000;100;10;1})),9)
 
@shrivallabha

Looking at OP requirement where he is mentioning that the character can go upto 24. I don't think Formula approach will be better I tested below formula for character up to O i.e. 15 number and the result are OK.

=RIGHT("00000000"&SUM(ISNUMBER(SEARCH($A$2:$O$2,A6))*(((COLUMN(INDIRECT("A:O"))^0)*10)^($A$3:$O$3-1))),15)

Here A2:O2 is {a,b,c,d,e,f,g,h,i,j,k,l,m,n,o} and A3:O3 is {15,14,13,12,11,10,9,8,7,6,5,4,3,2,1}

Note this is an array formula, so must be entered with Ctrl+Shift+Enter.

So, I will suggest to either go with a helper sheet or VBA function suggested by Narayan Sir.

Again all this is based on my understanding and there must be something which I am still Unaware of.

Regards,
 
Hi Kal ,

Use the following VBA , which is quite simple and straightforward :
Code:
Public Function Parse_and_Replace(InputCell As Range)
          Const ALPHABET = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z"
          Const OUTPUTLENGTH = 9
          Dim outputstring As String
          Dim Alpha_Array As Variant
       
          previndex = 0
          Alpha_Array = Split(ALPHABET, ",")
          For i = 1 To Len(InputCell)
              Character = Mid(InputCell.Value, i, 1)
              If Character <> " " Then
                  For j = previndex To UBound(Alpha_Array)
                      currchar = Alpha_Array(j)
                      If currchar = Character Then
                        outputstring = outputstring & "1"
                        Exit For
                      Else
                        outputstring = outputstring & "0"
                      End If
                  Next
                  previndex = j + 1
[quote="NARAYANK991, post: 115454, member: 1512"]Hi Kal ,

Use the following VBA , which is quite simple and straightforward :
[CODE]
Public Function Parse_and_Replace(InputCell As Range)
          Const ALPHABET = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z"
          Const OUTPUTLENGTH = 9
          Dim outputstring As String
          Dim Alpha_Array As Variant
        
          previndex = 0
          Alpha_Array = Split(ALPHABET, ",")
          For i = 1 To Len(InputCell)
              Character = Mid(InputCell.Value, i, 1)
              If Character <> " " Then
                  For j = previndex To UBound(Alpha_Array)
                      currchar = Alpha_Array(j)
                      If currchar = Character Then
                        outputstring = outputstring & "1"
                        Exit For
                      Else
                        outputstring = outputstring & "0"
                      End If
                  Next
                  previndex = j + 1
              End If
          Next
          Parse_and_Replace = Left(outputstring & String(UBound(Alpha_Array) - Len(InputCell), "0"), OUTPUTLENGTH)
End Function
This is a UDF , and in the worksheet cell , you would have :

=Parse_and_Replace(A2)

Copy this down.

if you later on wish to extend this to 24 characters , change the constant OUTPUTLENGTH from 9 to 24.

Narayan

End If
Next
Parse_and_Replace = Left(outputstring & String(UBound(Alpha_Array) - Len(InputCell), "0"), OUTPUTLENGTH)
End Function
[/CODE]
This is a UDF , and in the worksheet cell , you would have :

=Parse_and_Replace(A2)

Copy this down.

if you later on wish to extend this to 24 characters , change the constant OUTPUTLENGTH from 9 to 24.

Narayan[/quote]

Narayan,
I love this but I'm still getting a bit of trouble with it. When I extend it to 14 and 24

Narayan,
This is really neat. I love it, it works well for 9 chars however, I'm getting some issues when I tried it with 14 or 24 chars. It looks like when I use the full 14 or 24 chars in the input cell, it returns some value errors. I also notice for some strange reason, I have a cell with 7 chars and it returns an error. I' attaching my test file for you to take a look.
Thanks
Kal
 

Attachments

  • test04.xlsm
    19.3 KB · Views: 1
I completely missed the possibility of expanding the size to x.

Could you test below UDF?
Code:
Public Function ConvertLetters(rng As Range) As String
Dim lngMax As Long: lngMax = 9 'Change no. 9 to suit your base
Dim strBase As String: strBase = Replace(LCase(rng.Value), " ", "")
Dim strSource() As String
'\\ Work out the maximum number of chars required and build source string
For i = 1 To Len(strBase)
  If Asc(Mid(strBase, i, 1)) - 96 > lngMax Then
  lngMax = Asc(Mid(strBase, i, 1)) - 96
  End If
Next
'\\ Build up array with values based on finding letters
ReDim strSource(lngMax - 1)
For i = 0 To UBound(strSource)
  If InStr(strBase, Chr(i + 97)) > 0 Then
  strSource(i) = "1"
  Else
  strSource(i) = "0"
  End If
Next i
'\\ Join the array
ConvertLetters = Join(strSource, "")
End Function
 
I completely missed the possibility of expanding the size to x.

Could you test below UDF?
Code:
Public Function ConvertLetters(rng As Range) As String
Dim lngMax As Long: lngMax = 9 'Change no. 9 to suit your base
Dim strBase As String: strBase = Replace(LCase(rng.Value), " ", "")
Dim strSource() As String
'\\ Work out the maximum number of chars required and build source string
For i = 1 To Len(strBase)
  If Asc(Mid(strBase, i, 1)) - 96 > lngMax Then
  lngMax = Asc(Mid(strBase, i, 1)) - 96
  End If
Next
'\\ Build up array with values based on finding letters
ReDim strSource(lngMax - 1)
For i = 0 To UBound(strSource)
  If InStr(strBase, Chr(i + 97)) > 0 Then
  strSource(i) = "1"
  Else
  strSource(i) = "0"
  End If
Next i
'\\ Join the array
ConvertLetters = Join(strSource, "")
End Function
-------------------------------------------------------------
shrivallabha,
Wow! all I have to say is 'AWESOME!'.
This is great, it works like a charm. I set it 24 and use it with LEFT to get the amount char that I need.
Thank you guys. You are all amazing.
Kal
 
Thanks for the feedback.

The idea behind that UDF was to evaluate the maximum letter and adjust accordingly on its own. If you are going to use 24 then perhaps you can cut down the code as below:
Code:
Public Function ConvertLetters(rng As Range, Optional chrcnt As Long = 24) As String
Dim lngMax As Long: lngMax = 24
Dim strBase As String: strBase = Replace(LCase(rng.Value), " ", "")
Dim strSource(0 To 23) As String
'\\ Build up array with values based on finding letters
For i = 0 To UBound(strSource)
  If InStr(strBase, Chr(i + 97)) > 0 Then
  strSource(i) = "1"
  Else
  strSource(i) = "0"
  End If
Next i
'\\ Join the array
ConvertLetters = Left(Join(strSource, ""), chrcnt)
End Function

The use it like (No need for using LEFT). If you omit 2nd arg you will get 24 character long string:
=ConvertLetters(A2,9)
 
Thanks for the feedback.

The idea behind that UDF was to evaluate the maximum letter and adjust accordingly on its own. If you are going to use 24 then perhaps you can cut down the code as below:
Code:
Public Function ConvertLetters(rng As Range, Optional chrcnt As Long = 24) As String
Dim lngMax As Long: lngMax = 24
Dim strBase As String: strBase = Replace(LCase(rng.Value), " ", "")
Dim strSource(0 To 23) As String
'\\ Build up array with values based on finding letters
For i = 0 To UBound(strSource)
  If InStr(strBase, Chr(i + 97)) > 0 Then
  strSource(i) = "1"
  Else
  strSource(i) = "0"
  End If
Next i
'\\ Join the array
ConvertLetters = Left(Join(strSource, ""), chrcnt)
End Function

The use it like (No need for using LEFT). If you omit 2nd arg you will get 24 character long string:
=ConvertLetters(A2,9)
----------------------------------------------------

Perfect!!!!
Thank you
Kal.
 
Back
Top