+ Reply to Thread
Results 1 to 3 of 3

Can you use numbers formatted as text in formulas?

  1. 05-21-2019, 03:49 PM #1
    mconwayjr
    mconwayjr is offline
    Registered User
    Join Date
    10-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    14

    Can you use numbers formatted as text in formulas?

    Hello. I was hoping someone could help me with an issue I am facing in Excel. Unfortunately, I'm not an Excel guru so any help would be greatly appreciated!

    In the image below you will see that I am trying to search for every instance of VP in Column J to find the last number used in Column K and have the next available number shown on the Calculations tab.

    Excel help.jpg

    I recently had to change all the numbers in Column K to Text since there were preceding zeros and the numbers are not always the same number of digits(characters?) AND the formatting caused problems when importing the data from Excel into another program (Formatted as "Special" with Chinese characters?). It was working previously in the formula shown below but the numbers were formatted as "Special" showing some Chinese characters.

    Excel help2.jpg

    If anyone knows how I can find the next available sequence numbers while preserving the number (i.e. 001, 0003, etc), I would be very grateful!

    Thankyou,
    MCJ
    Attached Images Attached Images
    Last edited by AliGW; 05-21-2019 at 04:04 PM.
    Reply With QuoteRegister To Reply
  2. 05-21-2019, 03:58 PM #2
    mconwayjr
    mconwayjr is offline
    Registered User
    Join Date
    10-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    14

    Re: Can you use numbers formatted as text in formulas?

    Of course, after submitting this thread I stumbled on the solution while searching the web.

    My original formula was =MAX(IF('P&ID Input'!$J$2:$J$9999="VP",'P&ID Input'!$K$2:$K$9999))+1

    I found out that if I add "--" before the "IF" statement, it will treat the numbers formatted as text as numbers. So the new formula looks like this:
    =MAX(--IF('P&ID Input'!$J$2:$J$9999="VP",'P&ID Input'!$K$2:$K$9999))+1

    Hopefully this will help anyone else dealing with this issue.

    Thanks,
    MCJ
    Reply With QuoteRegister To Reply
  3. 05-21-2019, 04:05 PM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Can you use numbers formatted as text in formulas?

    Thanks for letting us know. I have correctly applied the thread solved tag for you.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM
  2. Extracting numbers and text from inconsistent text/number formatted string
    By Brandivil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 01:46 PM
  3. some numbers formatted as text
    By peterhw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2012, 01:59 PM
  4. some numbers formatted as text
    By peterhw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2012, 01:57 PM
  5. Some numbers formatted as text
    By peterhw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2012, 06:08 AM
  6. Excel 2007 : numbers formatted as text
    By pivottable in forum Excel General
    Replies: 4
    Last Post: 12-15-2011, 12:26 PM
  7. Numbers formatted as text
    By andrewc in forum Excel General
    Replies: 3
    Last Post: 05-20-2008, 09:02 AM
  8. [SOLVED] Converting numbers formatted as text to numbers
    By Bill in forum Excel General
    Replies: 1
    Last Post: 07-19-2005, 03:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1