+ Reply to Thread
Results 1 to 28 of 28

I want some part in edition formula in cell

  1. #1
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    I want some part in edition formula in cell

    Hi Friends,

    One cell contain =230+110+290.

    I want part of the above 3 numbers in another cell.

    Thank you.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I want some part in edition formula in cell

    Hi,

    Is this a string of text or a formula which results in 630?

  3. #3
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Manually entered.....

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,872

    Re: I want some part in edition formula in cell

    Do you have the FORMULATEXT function in Office 365? I am presuming you do. If so, you can use that along with the LEFT, MID and RIGHT functions to extract the numbers.

    e.g. =RIGHT(FORMULATEXT(A1),3)

    will result in 290.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Thank you AliGW,

    If we have 5 numbers.......

    Because some cells have 4 to 5 numbers...

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,872

    Re: I want some part in edition formula in cell

    OK - I've had fun trying to guess what your data looks like. How about posting a realistic sample with some expected outcomes?

  7. #7
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    We receive goods many times from one supplier, and we record like =22+35+210+31+11, like that. After that we can get any special scheme for some of it. Like 2nd delivered 35 have 2% rebate, want to prepare other form.

  8. #8
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    We have much more columns, so we dont want to create more columns for goods receiving. One of the reason is all are same price

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    Can you put this onto an Excel sheet, WITH your expected results? I'm not clear what you need...
    Glenn



  10. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I want some part in edition formula in cell

    Hi,

    Are these cells strings of text or a formula which results in 630.

  11. #11
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Strings of text

  12. #12
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    In below attached file, I want formula for B3 cell
    Attached Files Attached Files

  13. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I want some part in edition formula in cell

    You need to change one of your Excel options

    File...Options..Advanced scroll down to 'Display options for this sheet' and untick the checkbox

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    The one called "show formulas in cells instead of their calculated results" needs to be unchecked, to be precise...

  15. #15
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Yes, i know, i send you in that format to understand easly

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    Rajesh. You have confused us all, I think. Tell us all again EXACTLY what is in what cell and what you want done with it....

  17. #17
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Ok, Glenn,

    I have entered some data in B2, Total 5 numbers,
    I want one of that 5 numbers in B3. Some times 1st number, some times 2nd number etc. Let say in this sheet I want 4th Number in B3

    Thank you for your patiency.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    How/where will you indicate which one you want?

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    No reply... you're probably asleep. So try this out.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    let say I want formula for B3 like "B2 4th number"(my intention, not formula),

    Sorry, I don't know is it possible or not. I did't see any where like this, but my boss........

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,872

    Re: I want some part in edition formula in cell

    Quote Originally Posted by Glenn Kennedy View Post
    How/where will you indicate which one you want?
    I think Excel is meant to work it out from the contents of A3: 2% REBATE FOR 4TH DELIVERY

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    Rajesh. Does the sheet I attached come close?

  23. #23
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Excellent boss.... Thank you.....

  24. #24
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    One small request, Can we include C column number into formula, Then I can change number in Formula.......

  25. #25
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I want some part in edition formula in cell

    Quote Originally Posted by rajeshn_in View Post
    Ok, Glenn,

    I have entered some data in B2, Total 5 numbers,
    I want one of that 5 numbers in B3. Some times 1st number, some times 2nd number etc. Let say in this sheet I want 4th Number in B3

    Thank you for your patiency.
    Can I suggest that since you want to analyse a series of data values that you consider the way you are currently capturing and recording data. For any serious analysis you should collect data in a normalised 2 dimensional table. Once you have that then you can use Excel's various tools and functions to analyse it in many ways. This approach would be much simpler than jumping through hoops to interpret and analyse strings of varying lengths.

  26. #26
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    689

    Re: I want some part in edition formula in cell

    Yes, I saw the sheet, But in my sheet values have "=", because that cell must have total value.

  27. #27
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,788

    Re: I want some part in edition formula in cell

    Quote Originally Posted by rajeshn_in View Post
    One small request, Can we include C column number into formula, Then I can change number in Formula.......
    You could do that. but you would need to be mad to want to do so. The formula is fairly complicated. But, if (in D2) you wanted to change it, you would have to replace the 3 references to C2 with the number 3.

  28. #28
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I want some part in edition formula in cell

    Quote Originally Posted by rajeshn_in View Post
    Yes, I saw the sheet, But in my sheet values have "=", because that cell must have total value.
    You really are overcomplicating this.

    Record 5 records for Toys referencing each with (in this case) the digits from 1 to 5. Then the solution is either an autofilter or a simple lookup function.

+ 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. Replies: 3
    Last Post: 07-14-2016, 03:53 PM
  2. Formula to move part of a value to a new cell - part 2
    By forestavekids in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-28-2016, 12:34 PM
  3. Replies: 1
    Last Post: 03-06-2014, 07:14 PM
  4. [SOLVED] Formula for string edition
    By kaligad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-19-2013, 06:21 AM
  5. Index formula won't work in French edition
    By amy22x3 in forum Excel General
    Replies: 5
    Last Post: 02-23-2008, 03:45 PM
  6. Formula expected end of statement error, typing formula into cell as part of VBA macro
    By DavidGMullins@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2006, 03:05 PM
  7. How do I detect and commit the current cell edition?
    By ssissa@newsgroups.nospam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-10-2006, 11:45 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