+ Reply to Thread
Results 1 to 17 of 17

Numerical format problem (at least for me)

  1. #1
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Numerical format problem (at least for me)

    Hi Guys,
    I have a cell A18 which populates with data from another sheet.
    Thus =DATA!A2 which give me 205-8572073-5532359
    but I need it to format as *205-8572073-5532359*
    Any idea how I can force the * to prepend and append the numbers?

    Thanks in advance
    Last edited by And180y; 03-26-2020 at 10:50 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Numerical format problem (at least for me)

    Assuming you have a column of Data in Column A, you could run this macro to append the existing data.

    Please Login or Register  to view this content.
    How to install your new code
    • Copy the Excel VBA code
    • Select the workbook in which you want to store the Excel VBA code
    • Press Alt+F11 to open the Visual Basic Editor
    • Choose Insert > Module
    • Edit > Paste the macro into the module that appeared
    • Close the VBEditor
    • Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    • Press Alt-F8 to open the macro list
    • Select a macro in the list
    • Click the Run button

    EDIT: Sam's Solution is a better answer than what I have provided.
    Last edited by alansidman; 03-25-2020 at 08:24 PM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    this would be one way... ="*"&DATA!A2&"*"
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Thanks Alan,
    I neglected to say I only need the data in A18 which is copied from A2 in the other sheet. Sorry.

  5. #5
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Thanks Sam,
    not sure why but that throws big (unreadable) gaps in the barcode that gets generated.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    It works for the only example you gave. Maybe to get something better uploading an example workbook would be better?
    Follow the instructions in the yellow banner at the top of the post and make sure it is a representative example WITH expected results.

  7. #7
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Hi Sam,
    I hadn't attached an example as without the same barcode font people wouldn't see it at all. I could attach a pic of what I get manually adding the * and with the formula?

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    I don't see any attachment. Did you follow the directions at the top of the post?

  9. #9
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Hi Sam,
    the barcode on the left is generated with the formula and the one on the right by manually adding the *
    Attached Images Attached Images

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    Could you upload the (or a) workbook per the instructions at the top of the post? I am not able to replicate your snapshot so I could see how to account for it, the only thing I can do is guess that maybe adding some sort of an IF(ISNUMBER context to the formula and it would be a total guess because I can't even test for it.

  11. #11
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Hi Sam, do you need a copy of the barcode font as well (idautomation code 39)

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    I (or we for whomever works on it) need a representative sample of the data you are working with. Just exactly as it says in the yellow banner at the top of the post. Representative sample WITH expected results, if that means blanks where you have bar code info then that would be blanks would be included in the output.

  13. #13
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Hi Sam,
    workbook attached, sorry for misunderstanding. :-)
    Attached Files Attached Files

  14. #14
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    What you uploaded doesn't make a lot of sense to me. From post #1 where you want to point to Data A2 then in post #5 where you said you have unreadable gaps in the bar code that gets generated - I assumed (maybe wrongly) that your issue was in the Data tab. Now I do not know. I do not see the bar code you showed in post #9. I see that other cells in the Label tab have values in them from the Data tab but you haven't addressed what you are having trouble with or how you want information shown - expected results and where the issue/s occur with my formula.

  15. #15
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Hi Sam,
    the data in A2 (on the datasheet) is inserted into A18 on the label and then formulated as a barcode using the barcode font.
    The picture was posted because as I say without the barcode font installed all you will see is the formula which is what you are saying you see. :-)

  16. #16
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Numerical format problem (at least for me)

    Ok, now I understand, sorry for being so thick.
    I do not have a barcode font nor have I ever tried to create a barcode from a sample. So you needed the two asterisks added to help turn the info in Data A2 into a bar code?
    I'm only asking this now because hopefully it will help the next person who tries to assist you with more information.

  17. #17
    Forum Contributor
    Join Date
    02-28-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    365
    Posts
    308

    Re: Numerical format problem (at least for me)

    Sam no worries, my head was bursting with it all. lol
    Yes the simplest way I have found that works and scans on the label is to use a font
    rather than macros etc. I had downloaded fonts but couldn't get them to work and them stumbled on the asterisk to start and stop the code.
    I just need to work out how to get the asterisks to appear automatically when the data changes. Thanks for taking time to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] numerical format is not being recognized
    By waynelong in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2017, 05:28 AM
  2. Numerical format problem
    By optommy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2016, 09:43 AM
  3. Numerical values in a text format
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2010, 07:36 AM
  4. How to format TextBox output as numerical
    By knarf in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2009, 02:52 PM
  5. Numerical problem
    By aashrestha in forum Excel General
    Replies: 5
    Last Post: 05-16-2009, 05:30 AM
  6. [SOLVED] How do I preserve the numerical percentage format whe I do mailme.
    By Excel formats in Mailmerges in forum Excel General
    Replies: 3
    Last Post: 04-13-2005, 04:06 PM
  7. Can I set the numerical type of Cell to Hex format
    By hon123456 in forum Excel General
    Replies: 2
    Last Post: 01-13-2005, 09:06 AM

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