Results 1 to 7 of 7

Changing Order of Text and Numbers in a Cell

Threaded View

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    Desert Hot Springs, California, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question 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
    Last edited by drpaulmhine; 06-12-2012 at 05:45 PM. Reason: Pasting spreadsheet cells did not come out clearly in the actual posting.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

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