+ Reply to Thread
Results 1 to 16 of 16

Calculation two numbers with text n one cell

  1. #1
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Exclamation Calculation two numbers with text n one cell

    Hi Everyone,

    i have a question. I copied some datas from a system, then i paste to excel sheet. I would like to calculate the number in one cell, which is text included, is that possible? For details please open the excel file.

    Thanks
    Best Regard
    Alex
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,263

    Re: Calculation two numbers with text n one cell

    To get 44:

    =VALUE(LEFT(S8,FIND(" ",S8)-1))*(1-AS7)

    To get 10,500

    =VALUE(MID(S8,FIND(" ",S8)+3,6))*(1-AS8)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    10-22-2022
    Location
    Santa Cruz, Bolivia
    MS-Off Ver
    MSO 365
    Posts
    157

    Re: Calculation two numbers with text n one cell

    hi there, I allow me to propose this possible solution:

    Please Login or Register  to view this content.
    I hope it results useful.

  4. #4
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    Hi Logan,
    thanks for your answer. I tried your formula, but it didnt work. See attachment please.

  5. #5
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    Hi Logan,

    if the formula works on you, please send me the excel data to my email removed

    thanks alot
    Last edited by AliGW; 08-11-2024 at 01:23 AM. Reason: Unless you want an INBOX full of SPAM, please don't share personal details like this on a public forum. e have an attachment facility for this.

  6. #6
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    Thanks Bernie, there is still a problem on my excel, i dunno why.

  7. #7
    Forum Contributor
    Join Date
    10-22-2022
    Location
    Santa Cruz, Bolivia
    MS-Off Ver
    MSO 365
    Posts
    157

    Re: Calculation two numbers with text n one cell

    Quote Originally Posted by coki90 View Post
    Hi Logan,

    if the formula works on you, please send me the excel data to my email removed

    thanks alot
    here is the file with the proposal solution.

    project.xlsx
    Last edited by AliGW; 08-12-2024 at 09:38 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,306

    Re: Calculation two numbers with text n one cell

    Quote Originally Posted by coki90 View Post
    Thanks Bernie, there is still a problem on my excel, i dunno why.
    In Germany, you'll need semi-colons instead of commas. Open the attachment to post #7 and it should work.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,643

    Re: Calculation two numbers with text n one cell

    For Euro:

    Please Login or Register  to view this content.
    For Miles

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT

  10. #10
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    Hi Logan,

    Big Respect for you, i appreciate it what youve done for me. THANK YOU so Much for your help. Just let me know if you in Germany
    I know iam nor Excel Expert, but this forum really helped me.
    I only wanna ask you one more again Is it possible to have two decimals after comma (30,50 € ) for euro preis?

    Thanks

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,306

    Re: Calculation two numbers with text n one cell

    You can do it this way:

    =TEXTVERKETTEN(ZEICHEN(10);;TEXT(ZAHLENWERT(TEIL(WECHSELN(WECHSELN(WECHSELN(V8;".";"");"€";"");" ";WIEDERHOLEN(" ";50));{1;50};50);",")*(1-AS7:AS8);"#0,00")&{" €";" Miles"})

    Please acknowledge ALL assistance offered in your thread, not just one person's. Thanks.

  12. #12
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    Thanks you

    i have a question. Why is the formula only works on microsoft 365 apps. Im using this version now. It works. But as i sopied to another laptop, which has excel 2016 it doenst work properly. It shown like in the picture.

    =_xlfn.textjoin...........

    If i copied the right formula from you to this version (2016) it shown "#Name" in the cell. Can you give me tips?
    Thanks

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,306

    Re: Calculation two numbers with text n one cell

    Your profile says 2021. The TEXTJOIN formula was offered knowing that you were using 2021. You did NOT tell us in your opening post that your solutions needed to work in 2016 - if they do, then your profile needs to state that version.

    TEXTJOIN was a new function in Excel 2019 - no, it will NOT work in older versions. That's why you see the error.

  14. #14
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    Alright, im using the new version currently. But my other laptop has the oldversion. The solution is i must update to the new version

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,306

    Re: Calculation two numbers with text n one cell

    That wold be the optimum solution! If you are on 365, then please update your forum profile - it's still not right. Thanks.

  16. #16
    Registered User
    Join Date
    08-08-2024
    Location
    germany
    MS-Off Ver
    2021
    Posts
    8

    Re: Calculation two numbers with text n one cell

    alright , i will change it

+ 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] How to extract numbers for calculation when entering a string of text into a cell
    By choletseng in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-23-2024, 08:40 AM
  2. Replies: 6
    Last Post: 04-12-2023, 11:28 AM
  3. If statement ( Text and numbers calculation)
    By Shiraz50 in forum Excel General
    Replies: 5
    Last Post: 10-12-2022, 04:27 AM
  4. Calculation including table data, text and numbers
    By jackjstokes in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-18-2019, 04:39 AM
  5. Cell calculation - Text query ( Add / Subtract depending on text )
    By Glorioso999 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-14-2016, 10:33 AM
  6. [SOLVED] Calculation of numbers in a text box containing text and numbers
    By newbie4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2012, 01:29 AM
  7. Calculation of Text and Numbers
    By Dnyan in forum Excel General
    Replies: 10
    Last Post: 05-02-2012, 06:30 AM

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