Parsing a text string

%3CLINGO-SUB%20id%3D%22lingo-sub-2420389%22%20slang%3D%22en-US%22%3EParsing%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2420389%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehoping%20someone%20can%20help%20me.%20I%20am%20trying%20to%20find%20a%20way%20to%20separate%20a%20string%20which%20is%20a%20phonetic%20representation%20of%20a%20word%20into%20the%20separate%20phonetic%20symbols.%20I%20can%20work%20out%20how%20to%20split%20a%20text%20string%20and%20pick%20off%20one%20letter%20at%20a%20time%20using%20mid%20function%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMID(%24B7%2C%201%2C%201)%20for%20the%20first%20character%2C%26nbsp%3B%3DMID(%24B7%2C%202%2C%201)%20for%20the%20second%20and%20so%20on.%20CLucnky%20i%20know%20but%20it%20would%20work.%3C%2FP%3E%3CP%3EThe%20problem%20I%20have%20is%20some%20phonetic%20characters%20are%202%20characters%20long%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EThe%20word%20smiled%20is%26nbsp%3B%3CSTRONG%3Esma%C9%AAld%20%3C%2FSTRONG%3Ewhich%20would%20be%26nbsp%3Bs%20m%20a%C9%AA%20l%20d.%20%26nbsp%3Bthe%26nbsp%3Ba%C9%AA%20being%20the%20double%20character%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20pick%20off%202%20characters%20instead%20of%20one%20and%20then%20do%20a%20lookup%20to%20see%20if%20the%202%20characters%20are%20a%20valid%20phonetic%20character%20and%20if%20not%2C%20just%20use%20the%20one%20character.%20this%20works%20up%20to%20s%2C%20m%26nbsp%3Ba%C9%AA%20but%20then%20of%20course%20the%20next%20cell%20I%20am%20looking%20at%20starting%20on%26nbsp%3B%C9%AA%20because%20there's%20no%20way%20to%20know%20that%20I%20have%20already%20used%20this%20character.%20Is%20there%20a%20way%20to%20do%20this%20in%20excel%20or%20do%20I%20need%20VB%2C%20Javascript%3F%20(in%20which%20case%20I%20wouldn't%20know%20where%20to%20start!!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20any%20help!%20Fil%20attached%20to%20show%20where%20I%20am%20at%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2420389%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2420451%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2420451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1072328%22%20target%3D%22_blank%22%3E%40ToriMac%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook.%20It%20uses%20a%20custom%20VBA%20function%2C%20so%20you'll%20have%20to%20allow%20macros.%3C%2FP%3E%0A%3CP%3EPlease%20test%20thoroughly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2451402%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2451402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Amazing!!!!%20Thank%20you.%20How%20have%20you%20done%20this%3F%20I%20am%20not%20familiar%20with%20any%20coding%20in%20Excel.%20Can%20you%20tell%20me%20how%20I%20can%20see%20the%20code%20and%20see%20if%20I%20can%20edit%20it%20to%20do%20more%3F%20This%20is%20only%20the%20start%20of%20what%20I'd%20like%20to%20do.%20Wasn't%20sure%20if%20it%20would%20be%20possible.%20I'd%20first%20need%20to%20be%20able%20to%20do%20longer%20words.%20Example%20attached%20(but%20could%20be%20much%20longer%20too)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20this%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

4 Replies

@ToriMac 

See the attached workbook. It uses a custom VBA function, so you'll have to allow macros.

Please test thoroughly.

@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

@ToriMac 

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.

Thank you !!! I’ll take a good look at this over the next few days. Really appreciate it!

Vicki