Consonant Vowel convert words to CVCV

[forma]

Hi! Trying to figure out the formula to convert simple words to consonant vowel consonant format.

cat-->CVC
mama--->CVCV


I found this formula but it only gives me the first letter.

=IF(ISERROR(MATCH(A2,{"A";"E";"I";"O";"U"},0)),"C","V")

Thanks!

 

[FormR]

Hi, welcome to the board! You'll probably need a custom function for that and here is one option you can try:

Function CVCV(Word As String) As String
Dim i As Long
For i = 1 To Len(Word)
    CVCV = CVCV & IIf(InStr(1, "AEIOU", Mid(Word, i, 1), vbTextCompare), "V", "C")
Next i
End Function

To use:

1. With your spreadsheet open, press ALT+F11 to open the VBE.
2. Click "Insert" > "Module" on the menu bar.
3. Copy and paste the code below into the blank window on the top right hand side.
4. Press ALT+Q to close the VBE
5. Save your workbook as a macro enabled workbook (xlsm)
6. Use just like any another worksheet function as demonstrated below.

Excel 2013/2016
	A	B
1	cat	CVC
2	Dog	CVC
3	mama	CVCV
4	hello	CVCCV
Sheet1

Cell Formulas
Range	Formula
B1	=cvcv(A1)

 

[forma]

Thanks!
I am not well versed in excel. Any chance there is a simple formula that I can enter in the adjacent cell?

 

[FormR]

I am not well versed in excel

Hi, it's quite easy to implement the suggestion, steps 1-5 only need applying once for your workbook and after that you do use the formula directly in a cell on the worksheet.

Any chance there is a simple formula that I can enter in the adjacent cell?

It's unlikely to be "simple" but we might be able to use native functions if you have the TEXTJOIN() function available to you, can you see if you have it?

 

[Stiuart_W]

Thanks!
I am not well versed in excel. Any chance there is a simple formula that I can enter in the adjacent cell?

There are some much smarter users than I but FormR's solution is great because:-

1) it's what I would have suggested
2) He explains exactly how to do it
3) Getting to learn about VBA is always good and will help you in the future.
4) It's not obvious that a formula based solution would be possible, certainly not a 1 cell based solution - Eg you could break the string into individual characters convert the character to C or V and then re-combine but when the solution is very simple as above few are going to even attempt it

 

[forma]

I gave it a try under VBE. Its gives me "Error in Value"

I even tried this code.
I pasted under module and saved as macro.



Function CVCV(str As String) As String
Dim x As Long
For x = 1 To Len(str)
CVCV = CVCV & IIf(InStr(1, "AEIOU", Mid(str, x, 1), vbTextCompare), "V", "C")
Next x
End Function


Thanks for the help!

 

[FormR]

I gave it a try under VBE. Its gives me "Error in Value"

Hi, at what step did you get the error? what was the actual full text of the error? were any lines of code highlighted when you got the error (if so which)? and what version of Excel are you using?

The more information you can provide the better the chance of resolving the issue.

I even tried this code.

It looks the same except for the variables having different names

 

[Rick Rothstein]

Just so you know, FormR's function works fine for me. While we wait for your answers to the question FormR asked in Message #7 , I thought I would post an alternative function that should (can't be sure because I did not test it) execute marginally faster (it avoids the slow IIf function as well as the repeated concatenations), but if there is a time difference, you probably wouldn't see it unless you used the function in tens, if not hundreds, of thousands of cells.

Function CVCV(S As String) As String
  Dim X As Long
  CVCV = String(Len(S), "C")
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[AaEeIiOoUu]" Then Mid(CVCV, X) = "V"
  Next
End Function

 

[XOR LX]

Hi,

A formula-based solution is quite straightforward if you have Excel 2016 (with an Office 365 subscription).

If not, it's a little more difficult and restricted to strings of no more than 15 characters in length, with this array formula**:

=SUBSTITUTE(SUBSTITUTE(TEXT(NPV(-0.9,ISNUMBER(MATCH(MID(A1,1+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),{"A";"E";"I";"O";"U"},0))/10),REPT(0,LEN(A1))),1,"V"),0,"C")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Peter_SSs]

TEXTJOIN, if you have Excel 2016 (365 subscription), has been mentioned. Here is one such formula. It is also an array formula so must be confirmed with Ctrl+Shift+Enter as described by XOR LX

=TEXTJOIN(,,IF(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"aeiou")),"V","C"))