extracting letters from words with variable length and adding a string to the extracted letters

[bharath235]

for example: can a word in a cell "star" be extracted into another cell as "s.mp3,t.mp3,a.mp3,r.mp3"? the words can be a of varied length ..
the final output can be concatenated .. but the issue is that the words can be of any length .. example - "ant" or "butterfly" or "enlightenment" .. i reckon a loop needs to be created using vba .. could some one please help me out? thanks

 

[RatExcel]

Check the below code I've prepared. Just paste it into vba module and you can then use LettersFromWords function in your workbook.

Function LettersFromWords(strWord As String)
  
  LettersFromWords = vbNullString
  
  For i = 1 To Len(strWord)
    LettersFromWords = LettersFromWords & Mid(strWord, i, 1) & ".mp3;"
  Next i

End Function

 

[bharath235]

@RatExcel that was superb .. thank you ..

i am trying to build a JSON ..

the requirement is as follows:

for example the word "flap" will need to be extracted as follows:

[TABLE="width: 714"]
<colgroup><col></colgroup><tbody>[TR]
[TD] "app/assets/audio/f.mp3",[/TD]
[/TR]
[TR]
[TD] "app/assets/audio/l.mp3",[/TD]
[/TR]
[TR]
[TD] "app/assets/audio/a.mp3",[/TD]
[/TR]
[TR]
[TD] "app/assets/audio/p.mp3"

The last instance without a comma ...[/TD]
[/TR]
</tbody>[/TABLE]

I am going to tweak what you have prepared, however, i am a novice in VBA.. your help is much appreciated .. thx

 

[bharath235]

@RatExcel

I thin this works



Function LettersFromWords(strWord As String)

LettersFromWords = vbNullString

For I = 1 To Len(strWord)
LettersFromWords = LettersFromWords & Chr(34) & "app/assets/audio/" & Mid(strWord, I, 1) & ".mp3" & Chr(34) & "," & Chr(10)
Next I


End Function

except for the comma that comes by default .. the comma should not come for the last instance .. is that possible? Thx

 

[bharath235]

@RatExcel

i think .. i got it covered .. by using this

=LEFT(LettersFromWords(A54),LEN(LettersFromWords(A54))-2)

thx for your help

 

[Rick Rothstein]

Check the below code I've prepared. Just paste it into vba module and you can then use LettersFromWords function in your workbook.

Function LettersFromWords(strWord As String)
  
  LettersFromWords = vbNullString
  
  For i = 1 To Len(strWord)
    LettersFromWords = LettersFromWords & Mid(strWord, i, 1) & ".mp3;"
  Next i

End Function

The above function can be written without loops as a one-liner...

[table="width: 500"]
[tr]
	[td]Function LettersFromWords(strWord As String)
  LettersFromWords = Replace(StrConv(strWord, vbUnicode), Chr(0), ".mp3;")
End Function[/td]
[/tr]
[/table]

@RatExcel that was superb .. thank you ..

i am trying to build a JSON ..

the requirement is as follows:

for example the word "flap" will need to be extracted as follows:

[TABLE="width: 714"]
<colgroup><col></colgroup><tbody>[TR]
[TD] "app/assets/audio/f.mp3",[/TD]
[/TR]
[TR]
[TD] "app/assets/audio/l.mp3",[/TD]
[/TR]
[TR]
[TD] "app/assets/audio/a.mp3",[/TD]
[/TR]
[TR]
[TD] "app/assets/audio/p.mp3"

The last instance without a comma ...[/TD]
[/TR]
</tbody>[/TABLE]

Here is the above function modified to do what you are asking for here (notice the changed function name)...

[table="width: 500"]
[tr]
	[td]Function AppAssetsAudio(strWord As String)
  AppAssetsAudio = """app/assets/audio/" & Replace(Left(StrConv(strWord, vbUnicode), 2 * Len(strWord) - 1), Chr(0), ".mp3"",""app/assets/audio/") & ".mp3"""
End Function
[/td]
[/tr]
[/table]