+ Reply to Thread
Results 1 to 7 of 7

Changing Order of Text and Numbers in a Cell

  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.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Changing Order of Text and Numbers in a Cell

    Alternative:
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Changing Order of Text and Numbers in a Cell

    This formula should do it.....

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

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

    Wink 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. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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. #7
    Registered User
    Join Date
    06-12-2012
    Location
    Desert Hot Springs, California, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Changing Order of Text and Numbers in a Cell

    Quote Originally Posted by daddylonglegs View Post
    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

+ Reply to Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1