Jun 06 2021 02:54 PM
Hi all
hoping someone can help me. I am trying to find a way to separate a string which is a phonetic representation of a word into the separate phonetic symbols. I can work out how to split a text string and pick off one letter at a time using mid function:
=MID($B7, 1, 1) for the first character, =MID($B7, 2, 1) for the second and so on. CLucnky i know but it would work.
The problem I have is some phonetic characters are 2 characters long
Example:
The word smiled is smaɪld which would be s m aɪ l d. the aɪ being the double character one.
I can pick off 2 characters instead of one and then do a lookup to see if the 2 characters are a valid phonetic character and if not, just use the one character. this works up to s, m aɪ but then of course the next cell I am looking at starting on ɪ because there's no way to know that I have already used this character. Is there a way to do this in excel or do I need VB, Javascript? (in which case I wouldn't know where to start!!)
Many thanks in advance for any help! Fil attached to show where I am at
Jun 06 2021 03:49 PM
SolutionSee the attached workbook. It uses a custom VBA function, so you'll have to allow macros.
Please test thoroughly.
Jun 15 2021 03:29 PM
@Hans Vogelaar Amazing!!!! Thank you. How have you done this? I am not familiar with any coding in Excel. Can you tell me how I can see the code and see if I can edit it to do more? This is only the start of what I'd like to do. Wasn't sure if it would be possible. I'd first need to be able to do longer words. Example attached (but could be much longer too)
Thank you so much for this :)
Jun 16 2021 03:03 PM
Press Alt+F11 to activate the Visual Basic Editor.
If you don't see the code immediately, expand Modules in the pane on the left and double-click Module1.
The function currently looks like this:
Function ParseIPA(Word As String)
Dim i As Long
Dim j As Long
Dim c As Range
Dim n As Long
Dim r(1 To 20) As String
i = 1
Do
For j = 3 To 1 Step -1
Set c = Worksheets("SymbolsToCSNotationLookup").Range("A2:A53").Find(What:=Mid(Word, i, j), LookAt:=xlWhole)
If Not c Is Nothing Then
n = n + 1
r(n) = c.Value
Exit For
End If
Next j
i = i + Len(r(n))
Loop Until i > Len(Word)
ParseIPA = r
End Function
The line
Dim r(1 To 20) As String
specifies that the function returns at most 20 characters. If you need more, simply replace 20 with a larger number.
On the spreadsheet, you will have to select the corresponding number of cells in a row, enter the formula and press Ctrl+Shift+Enter if you don't have Excel in Microsoft 365.
Jun 16 2021 03:09 PM
Jun 06 2021 03:49 PM
SolutionSee the attached workbook. It uses a custom VBA function, so you'll have to allow macros.
Please test thoroughly.