+ Reply to Thread
Results 1 to 17 of 17

Function to add letter after number

  1. #1
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Function to add letter after number

    Hello everyone.

    I'm currently working with order sheets for flavoring company.
    When customer want to order flavor he need to put number (1,2,3,4,5 etc.) that means quantity of kg, and letter (like Q, K, C etc) that means which packaging of this kg.
    After customer fill up order sheet - putting everywhere number and letter - I can't anymore calculate in Excel what is the total order, and I need to calculate in by calculator.
    Order sheet consist of 5 columns:
    - name of flavor
    - and 4 columns to the right to put number and letter there for the order

    It is important number and letter stay at same cell for production.

    Can someone advice me better solution how to calculate
    - maybe formula that will count only number and disregard letters.
    - or formula where I can add letter to entire table where I have number after I get total for each column.

    Hope I explained it well and really hope someone can help me.

    Thank you a lot everyone.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Function to add letter after number

    Hi Olegs and welcome to the forum.

    It is important number and letter stay at same cell for production.
    If you have room for a helper column (it can be hidden) you could parse the number out with a formula like this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or embed that as part of your final formula.
    Dave

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Function to add letter after number

    Not too much information but I'll try

    I assume your digit/letter are in B1, C1, D1, E1, so try in eg. G1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and confirm by Control+Shift+Enter. If not you will get error. (with 999Z 34X... it will sum 9+9+9+3+4, hope there are no numbers but only digits )

    2017-02-21_020122.jpg
    Last edited by sandy666; 02-20-2017 at 09:05 PM.

  4. #4
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Thank you a lot for your help! It works, but need to have helper column.
    Thank you.

  5. #5
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Thank you a lot, I need to calculate by columns. It looks like thScreen Shot 2017-02-20 at 6.20.32 PM.png


    If I apply your formula it gives me "0", I even tried to change word "row" for "column".

    Thank you a lot!

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Function to add letter after number

    It's nice to know who you are "talking"

    a little modified Dave's formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    confirm by Control+Shift+Enter
    It will sum all numbers from column (B column in this case)

    ***change ranges suitably
    Last edited by sandy666; 02-20-2017 at 11:24 PM.

  7. #7
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Thank you a lot for your reply! )

    Tried to use it, but it does'n work, saying: "The formula you typed contains error"...
    Do you know by any chance why it is so?

    Thank you a lot for your help!

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Function to add letter after number

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  9. #9
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Thank you a lot for advice!
    Attached Files Attached Files
    Last edited by Olegs; 02-21-2017 at 03:38 AM.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Function to add letter after number

    No sample data in you file???

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Function to add letter after number

    Quote Originally Posted by Olegs View Post
    Thank you a lot for your reply! )

    Tried to use it, but it does'n work, saying: "The formula you typed contains error"...
    Do you know by any chance why it is so?

    Thank you a lot for your help!

    Control+Shift+Enter
    because formula is ARRAY
    You need to find how to use CSE on Mac. I am not happy Mac user so I don't know. Sorry.

  12. #12
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Sorry for this, please check now.

    Thank you a lot!

  13. #13
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Thank you a lot!!! )

  14. #14
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Function to add letter after number

    Try this in B40:

    =SUM(IFERROR(--LEFT(R[-39]C:R[-2]C,LEN(R[-39]C:R[-2]C)-1),""))

    Enter with Ctrl+Shift+Enter.

  15. #15
    Registered User
    Join Date
    02-20-2017
    Location
    California
    MS-Off Ver
    Excel for Mac 2011
    Posts
    8

    Re: Function to add letter after number

    Thank you a lot! It works.
    Greatly appreciate your help!

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Function to add letter after number

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Function to add letter after number

    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
    - mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,

    Thanks

+ 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. Using (right) function to find letter not number
    By dvent in forum Excel General
    Replies: 6
    Last Post: 01-25-2012, 11:59 AM
  2. Way to give cell number after letter with function.
    By lapator in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2009, 04:37 PM
  3. Replies: 17
    Last Post: 07-16-2009, 03:12 AM
  4. Format:change headers from letter to number/number to letter
    By Biff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  5. Replies: 2
    Last Post: 05-09-2005, 04:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1