+ Reply to Thread
Results 1 to 21 of 21

extract a number that is middle of characters and symbols+one another

  1. #1
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    extract a number that is middle of characters and symbols+one another

    Respected Sir/Madam,
    Has to extract a number that is middle of characters and symbols
    +
    one another


    Zohar
    Attached Files Attached Files
    Last edited by AliGW; 01-27-2020 at 09:53 AM. Reason: Urgency statement removed from thread title and post.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    51,040

    Re: extract a number that is middle of characters and symbols+one another

    Please note: all queries are treated the same here. please don't ask for or expect yours to be treated with urgency. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Madam AliGW,
    SORRY for that
    but
    after getting this formula , I have to make 7-8 formulas as per the data and repeat(paste) it in a 1000+ days data.
    and the line manager is getting impatient. BTW I am from india and now here it is 8 pm.
    Zohar

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,265

    Re: extract a number that is middle of characters and symbols+one another

    =--LEFT(SUBSTITUTE(MID(A3,FIND("║NF‰",A3)+LEN("║NF‰"),LEN(A3))," ",REPT(" ",LEN(A3))),LEN(A3))
    LEN("║NF‰")could be replaced to number and LEN(A3) to number not less then light of source

  5. #5
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected,
    Thank you BMV,
    The second one is surely very easy compared to this. PLEASE PLEASE look it.
    ---



    Zohar
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected BMV,
    There is some problem in making it to calculate other combinations.
    PLEASE help me.
    Zohar
    Attached Files Attached Files

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: extract a number that is middle of characters and symbols+one another

    In G3
    =TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A3,"%",REPT(" ",255)),255),"NF‰",REPT(" ",255)),255))
    Copy down


    In G12
    =TRIM(RIGHT(SUBSTITUTE(A12,"║10DA",REPT(" ",255)),255))
    Copy down


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

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  8. #8
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Sir Ace_XL
    THANK YOU and also please solve the other problem that is on 24th row.
    Zohar
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    51,040

    Re: extract a number that is middle of characters and symbols+one another

    And the line manager is getting impatient.
    Absolutely of no interest or consequence to us, I am afraid. The help you get here is given by volunteers in their own time and for free, so please don't treat it like a paid service. Thank you for your understanding.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    51,040

    Re: extract a number that is middle of characters and symbols+one another

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  11. #11
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Madam AliGW,
    That is not the Post I have posted Here.
    I will post that URL and the Query both.

    https://www.ozgrid.com/forum/index.p...32#post1229332

    Zohar
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,601

    Re: extract a number that is middle of characters and symbols+one another

    Thank You for providing the link.
    The five possible outcomes are displayed in cells K22:K26
    The formula for cells E25:E29 is: =IF(AND(A25-C25>D25,C25-B25>D25),K$26,IF(C25-B25=0,K$22,IF(A25-C25=0,K$25,IF(A25-C25<C25-B25,K$24,K$23))))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  13. #13
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Sir JeteMc,
    Really I am spellbound, you have really helped me very much .THANK YOU THANK YOU VERY VERY MUCH.
    Can I replace outcomes in place of Static addressed $K$22, $k$23, $k$24, $k$25, $k$26??
    And Can you please edit the formula where in WLN▲ WLN▼, the case triggers even when Last is near 0.001% of high or low
    So the case will be
    High Low
    8441 8390.15 8390.15 16.78 WLN▼
    8441 8390.15 8389.55 upto 8389.31 or 8390.98 as 0.83 is 0.001%
    8416 8384.5 8416 16.83 WLN▲
    8416 8384.5 8416.8 upto 8415.17 or 8416.83 as 0.83 is 0.001% (this figures are in two decimals(which is given in data source)

    THANK YOU ONCE AGAIN

    Zohar

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,601

    Re: extract a number that is middle of characters and symbols+one another

    If I understand correctly then I believer you can replace the formula in E25 with:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  15. #15
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Sir JeteMc,
    I had tried to incorporate ranges in formula but due to some mistake(which I was not able to find) I am getting wrong results , so please help me.
    For my work Incorporating range is important as there are about 13 places where this formula has to be inserted and non has extra cells for specifying range.
    Zohar
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Sir JeteMc,
    Thank you for extending your help.
    I am very SORRY for not seeing your post before writing another post. AGAIN SORRY.

    Have 2 more case where WLN▼ should be trigerred even if last above low by 0.001%-(originally fast gust at last movement may change the level)
    WLN▲ should be trigerred even if last Below high by 0.001%

    Has attached excel sheet indicating this
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,601

    Re: extract a number that is middle of characters and symbols+one another

    Let me attempt to state my understanding of how the formula should incorporate ranges:
    1. If Last is not between the High and Low then OR
    2. If Last is within 0.001% of High then WLN▲
    3. If Last is within 0.001% of Low then WLN▼
    4. If Last is closer to High than Low then LN▲
    5. If Last is closer to Low then High then LN▼
    If all of the above are correct then try the following in cells E12 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If the above 5 statements are not all correct then please provide the correct information.
    Let us know if you have any questions.

  18. #18
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Sir JeteMc,

    I am very SORRY because the last file I uploaded was the non-complete version . So am uploading again. AGAIN SORRY.
    Mistakes-one case is shown to show last value that is even lower than the low and that is not possible.
    other case is shown to show last value that is even higher than the high and that is not possible.


    Have 2 more case where WLN▼ should be trigerred even if last above low by 0.001%-(originally fast gust at last movement may change the level)
    WLN▲ should be trigerred even if last Below high by 0.001%

    has also posted on other forum
    https://www.ozgrid.com/forum/index.p...09#post1229462

    Zohar

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,601

    Re: extract a number that is middle of characters and symbols+one another

    ...the last file I uploaded was the non-complete version . So am uploading again.
    If there is supposed to be an .xlsx file attached to post #18, I am not seeing it.
    Question: by 0.001% do you mean one-thousandths of one percent (0.00001)?
    Question: What does "originally fast gust at last movement may change the level" mean and is it important to take into consideration in writing a formula?

  20. #20
    Registered User
    Join Date
    03-29-2004
    Location
    vadodara, gujarat, india
    Posts
    73

    Re: extract a number that is middle of characters and symbols+one another

    Respected Sir JeteMc,
    I was outstation and so was not able to answer since last 3 days. SORRY
    Question: by 0.001% do you mean one-thousandths of one percent (0.00001)?
    Answer yes -have tried on that type of scenario in excel
    Question: What does "originally fast gust at last movement may change the level" mean and is it important to take into consideration in writing a formula?
    Answer -sometimes(originally once in a fortnight-but that not important ) it happens that after noting down the last level and closing the gates , the water level is at brim of gates(as it is on WLN▲ or WLN▼ and due to fast movement of water in upper & lower reservoir that end level changes as some water spills back to main reservoir. so we have to consider that also,


    I think the issue is solved at this time, Will need your help when something that I am not able to understand comes while applying this in the main file as that are low of dependents of this figure.

    Zohar
    Attached Files Attached Files
    Last edited by ZOHAR; 02-05-2020 at 12:36 AM.

  21. #21
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,601

    Re: extract a number that is middle of characters and symbols+one another

    Glad that the issue is resolved to your satisfaction. Please mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Counting the total number of symbols or characters in multiple rows
    By wgwg123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2011, 08:16 PM
  2. Counting the total number of symbols or characters in multiple rows
    By wgwg123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2011, 07:49 PM
  3. Extract number in middle of cell
    By Jason Morin in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 07:05 AM
  4. Extract number in middle of cell
    By Jason Morin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Extract number in middle of cell
    By SCOOBYDOO in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. [SOLVED] Extract number in middle of cell
    By SCOOBYDOO in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Extract number in middle of cell
    By SCOOBYDOO in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. [SOLVED] Extract number in middle of cell
    By SCOOBYDOO in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2005, 11:05 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