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

## Bookmarks