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