# Changing Order of Text and Numbers in a Cell

1. ## Changing Order of Text and Numbers in a Cell

I have a spreadsheet with 679 entries that gives the name and site number of the residents at the resort where I live in a single "resident" column. This comes up on a keypad for secure entry to our resort.

Whoever programmed this originally put the names and site numbers in the wrong order.

I'd like to change the format from "105 Wedell, G" to "Wedell, G 105" for each of the 679 sites using an Excel formula.

I think I'm getting pretty close using the following:

=RIGHT(B17,FIND(",",B17)-2)&" "&LEFT(B17,FIND(",",B17,1)-8)

The input is as follows:

112 HOLMES, B
113 KABELA, J
114 KROS, M
115 STIMAC, S
116 ROWAN, M
117 PRESTON, D
118 WEST, L&D
119 HOUGLUM, L
12 TRUMBLY, A
120 FOSSAN, C
121 OPSETMOEN
122 DAWES, R
123 EDNEY, E&G
124 PATTISON, J
125 PRODAN, I
126 SIMMONS, J

The output is as follows:

HOLMES, B 112
KABELA, J 113
KROS, M 1
STIMAC, S 115
ROWAN, M 11
PRESTON, D 117
ST, L&D 1
HOUGLUM, L 119
RUMBLY, A 12
FOSSAN, C 120
#VALUE!
DAWES, R 12
NEY, E&G 12
PATTISON, J 124 P
PRODAN, I 125
SIMMONS, J 126

Some of them come out fine, like 112, 113, 115, 116, etc but, there are anomolies, e.g. 114, 116, 118, 121, etc

I think I'm on the right track, but there's something in the formula that I am missing by just putting in that "8" at the end. I thinkIi would need to put in a "length" function there to account for the variation in name length. Also, I get a #VALUE! if there is no comma present at all.

Any help would be greatly appreciated!

Paul

2. ## Re: Changing Order of Text and Numbers in a Cell

Paul,

Welcome to the forum!
Give this formula a try:
``Please Login or Register  to view this content.``

3. ## Re: Changing Order of Text and Numbers in a Cell

Alternative:
``Please Login or Register  to view this content.``

4. ## Re: Changing Order of Text and Numbers in a Cell

This formula should do it.....

=MID(B17&" "&B17,FIND(" ",B17)+1,LEN(B17))

5. ## Re: Changing Order of Text and Numbers in a Cell

Thank you both.

All three formulas work ... except for one particular format.

Some of my sites are not filled with residents right now, so all they have in the column to be converted is a space number, e.g. 106 ... without anything else.

Using any of these formulas still returns a #VALUE! result.

Can anything be done about this?

If not, I'll just manually change those.

Thanks again.

Paul

6. ## Re: Changing Order of Text and Numbers in a Cell

What do you want the result to be in that case? If you use this slightly amended version of my suggestion.....

=MID(B17&" "&B17,FIND(" ",B17&" ")+1,LEN(B17))

....then if there is no space in B17 the formula will simply return the contents as now

7. ## Re: Changing Order of Text and Numbers in a Cell

Originally Posted by daddylonglegs
What do you want the result to be in that case? If you use this slightly amended version of my suggestion.....

=MID(B17&" "&B17,FIND(" ",B17&" ")+1,LEN(B17))

....then if there is no space in B17 the formula will simply return the contents as now
BRILLIANT!

Thank you very much daddylonglegs.

You just saved me several hours of figuring it out, which of course, I could have used to do it all manually!

HAHA

Paul

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1