Excel 2011- how to store numbers as text?

Discussion in 'Mac Apps and Mac App Store' started by rbrian, Jan 28, 2012.

Most Liked Posts
  1. rbrian, Jan 28, 2012

    rbrian macrumors 6502a

    rbrian

    Joined:
    Jul 24, 2011
    Location:
    Aberdeen, Scotland
    #1
    The original cell contains numbers with quotes, e.g. '12345' which of course makes it text. I use =REPLACE to remove the quotes, leaving just 12345, which is a number, but still stored as text - even after formatting the cell as a number.

    I need to look it up on another sheet which has the same number stored as a number, and =VLOOKUP can't find it! On my work laptop, running Vista and Excel 2003, I can copy the number into NotePad, and copy it back again to find it's magically converted to a number stored as text, and =VLOOKUP works.

    I tried the same thing with Lion, Excel 2011, and TextEdit, and it didn't work - the number is still a number. I tried reformatting the cell as text, and the number jumps to the left, so it looks like text, but =VLOOKUP still can't find it.

    I need something as simple as NotePad - it seems TextEdit is too clever!
     
    share
    + Quote Reply
  2. James Craner, Jan 28, 2012

    James Craner macrumors 68000

    James Craner

    Joined:
    Sep 13, 2002
    Location:
    Bristol, UK
    #2
    Microsoft Excel-1.png

    You need to use the Mid function to remove the leading and trailing ' from the text string. The syntax of Mid is MID(text,start_num,num_chars) Start_num is 1, and num_chars will be the length of the text string less 1 (as you don't want the trailing ', which you can get using Len function.

    This function then needs to be nested in the Value formula to turn the text string into a value. Hope this helps.
     
    share
    + Quote Reply
  3. jlc1978, Jan 28, 2012
    Last edited: Jan 28, 2012

    jlc1978 macrumors 68020

    jlc1978

    Joined:
    Aug 14, 2009
    #3
    That is odd - i tried various combinations of the value function and text functions that did not work. Excel must have some weird internal storage method. They'd work once but them not on a recalculation.
     
    share
    + Quote Reply
  4. rbrian, Jan 28, 2012

    rbrian thread starter macrumors 6502a

    rbrian

    Joined:
    Jul 24, 2011
    Location:
    Aberdeen, Scotland
    #4
    Thanks for your reply. I'll have a go tomorrow, or Monday. It'll require a re-write of several sections of the sheet, and I can't be bothered right now!
     
    share
    + Quote Reply
  5. rbrian, Jan 28, 2012

    rbrian thread starter macrumors 6502a

    rbrian

    Joined:
    Jul 24, 2011
    Location:
    Aberdeen, Scotland
    #5
    Oh yes, and one more thing, is there a function that will search an array and find a result not from a specific column? I want to be able to say "Search for C1, in range D1:BG1000, return the cell one to the right of where you found it".

    =VLOOKUP doesn't do it, which means I have to use some awkward workarounds - stacking all the column pairs in columns A and B - which get to be over 50,000 rows long.
     
    share
    + Quote Reply
  6. saberahul, Jan 28, 2012

    saberahul macrumors 68040

    Joined:
    Nov 6, 2008
    Location:
    USA
    #6
    You said one to the right. Wouldn't that be HLOOKUP instead of VLOOKUP?
     
    share
    + Quote Reply
  7. rbrian, Jan 28, 2012

    rbrian thread starter macrumors 6502a

    rbrian

    Joined:
    Jul 24, 2011
    Location:
    Aberdeen, Scotland
    #7
    Then I'd have to specify a particular row for the result. It could be any one of a 1000 rows, and any one of 50 columns.

    Both =VLOOKUP and =HLOOKUP will only take a result from a specified column or row.

    I need =ALOOKUP, but such a thing doesn't exist. There may be an alternative though, I was hoping someone might know.
     
    share
    + Quote Reply
  8. dooban, Jan 28, 2012

    dooban macrumors newbie

    Joined:
    Jul 21, 2011
    #8
    Once quotes are removed, have you tried multiplying the "string number" by one ? It should give you the number.
     
    share
    + Quote Reply
  9. James Craner, Jan 28, 2012

    James Craner macrumors 68000

    James Craner

    Joined:
    Sep 13, 2002
    Location:
    Bristol, UK
    #9
    Well there is not a built in function that can do this, however we can create a User Defined Function using VBA.

    The following code creates a UDF called findvalue to paste it in as a VBA Module - Click Tools > Macro > Visual Basic Editor

    Then right click on the sheet that you have the data table and choose Insert >Module

    Microsoft Excel-2.png

    On the module Sheet insert the following code

    Function FindValue(Search_Value As Variant, Lookup_Range As Range) As String
    FindValue = "#N/A" 'If value not found
    For Each c In Lookup_Range
    If c.Value = Search_Value Then
    FindValue = c.Address
    Exit For
    End If
    Next
    End Function

    Then take a look at my sample sheet for the formula

    Microsoft Excel-3.png

    Note : Table is a defined cell range B3:E14

    The findvalue function returns the cell reference as text, the INDIRECT function returns a cell reference and the Offset function is used to return the value in the cell to the right.

    Hope this solves the problem for you.
     
    share
    + Quote Reply
  10. rbrian, Feb 2, 2012

    rbrian thread starter macrumors 6502a

    rbrian

    Joined:
    Jul 24, 2011
    Location:
    Aberdeen, Scotland
    #10
    Thanks for your help guys. I've now used =VALUE(MID) to extract the number, then multiplied by 1 - and it still doesn't work! I'll have to continue using notepad to convert the numbers in the lookup sheet to text.

    Now for one more question, this one more about ethics than Excel. I started this project, which I estimate will save me around an hour a day, after seeing a colleague on the night shift using the same thing. He refused to share, reasoning (correctly) that if management saw it, they would expect more work out of us.

    Now I've moved to the day shift, and decided to build my own spreadsheet. It's been quite successful, thanks to yourselves, Google, and the inspiration of my night shift colleague. Working in the day, naturally I come into contact with managers, who are conspicuous by their absence at night.

    They've seen my spreadsheet, and some tools which were heavily modified by the night shift, and are so impressed, they'd like me to make a presentation with a view to rolling it out nationally. It's a feather in my cap, and is likely to lead to promotion. My night shift colleague has asked me not to present anything that he worked on.

    Most of the work is my own, but I don't know if I would even have started the project before seeing what was possible. I have taken advice from various sources, including all of you. Do any of you want to veto my presentation, since it contains some of your work? Do you have the right? Should you have the right?
     
    share
    + Quote Reply
  11. exegete77, Feb 2, 2012

    exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #11
    I worked for 8 years in a Fortune 50 company. I reduced one process from 45+ days to less than an hour. I learned much from books, and a couple Excel sites where international MS VIP's participated. Not once did they ever want credit. I had posed a problem to do something that was rather difficult, and time consuming (replace hundreds of items in 30,000 rows). There were several excellent solutions. Finally one person used a method that even the best had not ever tried or used. With modication, it I was able to use it and it accomplished the replacements in less than 2.5 seconds. Again, not once was anyone on the entire board (MrExcel.com) looking for credit let alone a demand not to use.

    And I think that VBA code and formulas like this are not copyrighted. And if he did it for the company on company computers, then it does not belong to him or you. It belongs to the company... look at the statement the company has regarding that.
     
    share
    + Quote Reply
  12. James Craner, Feb 3, 2012

    James Craner macrumors 68000

    James Craner

    Joined:
    Sep 13, 2002
    Location:
    Bristol, UK
    #12
    I have no problem with you using any thing that I posted here - I just want to help. I should mention though that the VBA code is not mine, it is something I found on a site like MrExcel, but I can't remember where I got it from as I have had it for a while, but since it was posted to a help forum I am sure he/she would not mind it being used.
     
    share
    + Quote Reply
(You must log in or sign up to post here.)

Share This Page