# Text Function Troubles

#### Beachcomber

##### New Member
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.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.

Code:
``````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

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
ABCD
1dreameamdr
2WednesdayednesdayW
3eateat
4flyfly
6threeeethr
7aa
8two wordso wordstw
9
Rearrange Text

Beachcomber said:
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.

Book6
ABCD
1
2dreamaedrm
3WednesdayaeWdnsdy
4eataet
5flyfly
7threeethr
8aa
9two wordsotw 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.

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.

...another alternative

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

barry houdini said:
...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?

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

barry houdini said:
...another alternative

=MID(A1&A1,MIN(FIND({"a","e","i","o","u"},A1&"aeiou")),LEN(A1))
I like that Barry!

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?

Replies
4
Views
137
Replies
4
Views
196
Replies
5
Views
464
Replies
2
Views
403
Replies
6
Views
969

1,203,759
Messages
6,057,198
Members
444,913
Latest member
ILGSE

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back