Forum Discussion
Parsing a text string
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
See the attached workbook. It uses a custom VBA function, so you'll have to allow macros.
Please test thoroughly.
4 Replies
See the attached workbook. It uses a custom VBA function, so you'll have to allow macros.
Please test thoroughly.
HansVogelaar 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 🙂
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 FunctionThe line
Dim r(1 To 20) As Stringspecifies 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.
