Text Function Troubles

[Beachcomber]

I'm trying to manipulate a text string by finding the location of the first vowel within the string (a,e,i,o,u), then taking all the characters up to the vowel and placing them at the end of the text string.

For example, if we have the text string "dream", the result would be "eamdr".

I've experimented with the FIND() function to locate the vowel within a text string (if it exists), then simply using LEFT() with the number of characters up to the first vowel to paste that at the end of the original text string. For simplicity I'm only concerned about lower case vowels (a,e,i,o,u). However, I'm experiencing difficulty trying to bring it all together. Any suggestions? Any help would be appreciated.

 

[Legacy 98055]

No doubt that there is a way to do this natively, but I don't know how. Here is a UDF that seems to work.

Option Compare Binary

Function ToVowel(Text As String) As String
    Dim i As Integer
    
    For i = 1 To Len(Text)
        If Mid(Text, i, 1) Like "[a,e,i,o,u]" Then Exit For
    Next
    
    ToVowel = Trim(Mid(Text, i, Len(Text)) & Left(Text, i - 1))
End Function

Example usage:

dream in cell A1
=ToVowel(A1) in some other cell
Output is eamdr

 

[Peter_SSs]

Beachcomber

Like this? Formula in B1 (copied down) is:
=RIGHT(A1,LEN(A1)-MIN(SEARCH({"a","e","i","o","u"},A1&"aeiou"))+1)&LEFT(A1,MIN(SEARCH({"a","e","i","o","u"},A1&"aeiou"))-1)

Mr Excel.xls
	A	B	C	D
1	dream	eamdr
2	Wednesday	ednesdayW
3	eat	eat
4	fly	fly
5	thread	eadthr
6	three	eethr
7	a	a
8	two words	o wordstw
9
Rearrange Text

 

[Aladin Akyurek]

I'm trying to manipulate a text string by finding the location of the first vowel within the string (a,e,i,o,u), then taking all the characters up to the vowel and placing them at the end of the text string.

For example, if we have the text string "dream", the result would be "eamdr".

I've experimented with the FIND() function to locate the vowel within a text string (if it exists), then simply using LEFT() with the number of characters up to the first vowel to paste that at the end of the original text string. For simplicity I'm only concerned about lower case vowels (a,e,i,o,u). However, I'm experiencing difficulty trying to bring it all together. Any suggestions? Any help would be appreciated.

If you have the latest version of the morefunc.xll add-in...

Book6
	A	B	C	D
1
2	dream	aedrm
3	Wednesday	aeWdnsdy
4	eat	aet
5	fly	fly
6	thread	aethrd
7	three	ethr
8	a	a
9	two words	otw wrds
10
Sheet1

B2:

=MCONCAT(IF(ISNUMBER(SEARCH({"a","e","i","o","u"},A2)),{"a","e","i","o","u"},""))&REGEX.SUBSTITUTE(A2,"a|A|e|E|i|I|o|O|u|U","")

which is confirmed with control+shift+enter then copied down.

The whole thing should be done using just REGEX (regular expressions) functions.

 

[Beachcomber]

Thank you for your help everyone! I tried out Right_Click's UDF which was really cool. I think I am going to go with Peter's formula, because it is the simplest. Aladin - thanks for the special formula, I'm going to download the morefunc.xll addon and give it a try.

Once again, many thanks for your assisstance, it is greatly appreciated. I also thought of another way to extract the text, using the ISERR() & MIN() functions along with FIND() to determine if a certain character exists, then find the MIN() of that location, but Peter's function seems to be much more efficient.

 

[barry houdini]

...another alternative

=MID(A1&A1,MIN(FIND({"a","e","i","o","u"},A1&"aeiou")),LEN(A1))

 

[Aladin Akyurek]

...another alternative

=MID(A1&A1,MIN(FIND({"a","e","i","o","u"},A1&"aeiou")),LEN(A1))

Although the OP does not seem to be bothered with it, shouldn't

aeWdnsdy

(or maybe: eeaWdnsdy)

be returned for:

Wednesday?

 

[barry houdini]

Aladin,

my assumption was that Wednesday would become "ednesdayW", moving all characters before the first vowel to the end of the string, as per Peter's examples

 

[Peter_SSs]

...another alternative

=MID(A1&A1,MIN(FIND({"a","e","i","o","u"},A1&"aeiou")),LEN(A1))

I like that Barry!

 

[UHsoccer]

When I use the FIND function to locate a string and the string is not found, it returns #VALUE!
I tried to use

if (Find(A2,"XX")="#VALUE!",0,Find(A2,"XX"))

To no avail

What am I doing wrong?