+ Reply to Thread
Results 1 to 23 of 23

Nesting formula and formatting cells as text not numbers

  1. #1
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Nesting formula and formatting cells as text not numbers

    I currently have a workbook containing various data from which I am extracting only the information I need. Most columns are fine but new columns F & G are trying to extract data from columns L & M and this is where I need help. I want to end up with ONLY column G which should be the mobile telephone numbers of the candidate. I do not want the words "Mobile: ", nor do I want the unnecessary "+44" or "-" or "044" etc. If there is no mobile number entered in column M I then want to pick up the number contained in column L, again without the same unnecessary "Phone" or"+44" or "-" or "044" etc. I also want the end resulting column to be formatted as text rather than a number, with a leading 0 in front of the final tel no. ie 07788665544.

    Thank you very much for any help - I am, as is fairly obvious, a novice as functions, nesting, etc
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    Welcome to the forum.

    This will deal with the either/or scenario:

    =TRIM(IF(MID(M1,FIND(":",M1)+1,99)="",MID(L1,FIND(":",L1)+1,99),MID(M1,FIND(":",M1)+1,99)))

    Dealing with the codes is harder - whilst some logic can be applied to the variations you have, it's going to be hard for Excel to decide what to do in the case of a number that begins +44, for example, UNLESS the + is only ever followed by a two-digit code.

    I think what you need to do is provide a new sample workbook with just the phone number columns and more examples of possible layouts. Then add a manually created column showing what you want the formula to return.

    We will need 10-15 rows of sample data.
    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
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Thank you Ali GW - I have added this code and it works brilliantly for the either or scenario. VERY much appreciated and I will continue to plug away at the phone number formats. The number formats do vary but + is only ever followed by 44 ...... How do I add SUBSTITUTE,"+44","" in the correct format to the above formula?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    You could try this:

    =SUBSTITUTE(TRIM(IF(MID(M1,FIND(":",M1)+1,99)="",MID(L1,FIND(":",L1)+1,99),MID(M1,FIND(":",M1)+1,99))),"+44","")

    Let us know if you want further help with displaying a leading zero.

    Otherwise, 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.

  5. #5
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Thanks again Ali GW - that really really helps. How do I add more SUBSTITUTE functions to this current string - for instance I want to take the "-" out of 07899-407 969 by substituting it with nothing ["-", ""] and the additional superfluous space between digits 7 and 9 with nothing [" ",""] and so on and so forth for all the variations in the phone format. However I simply don't know how to add to the existing formula (my brain has almost ceased functioning - I have done it on other bits of the spreadsheet but can't fathom it here ...), at what point I should add etc.

    I am OK at formatting the resulting with a leading zero by just custom formatting the column as " 0########## " but if there is a way of also doing that dynamically then I would be very grateful for help on that too. However do I need to start a new thread for this latter issue????

    So appreciate your help / advice so far - saves me from screaming in frustration ....., I will certainly be giving you a small star icon ....

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    Just keep substituting in a nested fashion thus:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(MID(M1,FIND(":",M1)+1,99)="",MID(L1,FIND(":",L1)+1,99),MID(M1,FIND(":",M1)+1,99))),"+44",""),"-","")," ",""),"0(44)","")

    Then wrap it all in a TEXT function:

    =TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(MID(M1,FIND(":",M1)+1,99)="",MID(L1,FIND(":",L1)+1,99),MID(M1,FIND(":",M1)+1,99))),"+44",""),"-","")," ",""),"0(44)",""),"0##########")

    You are going to have to think about how to deal with 44 - SUBSTITUTE would remove any 44 combination from the string!

  7. #7
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    THANK YOU !! I know I must appear extremely thick but how do I ...."Just keep substituting in a nested fashion thus:" ? Go to the beginning of the beginning of the formula and just type in SUBSTITUTE followed by a bracket and then add on the old text, new text at the end, separating them by commas, quotations marks and finishing with a closing bracket - is it that easy ? I managed to add to formulas yesterday but my brain in screaming at me to give up for the night but I can't until I have understood (with your invaluable help )the process of how to add to existing formula

    Also then how do I then ..................."Then wrap it all in a TEXT function: ...."?

    I agree that the straight 44 substitute needs more thought - can I use the RIGHT function / formula combined with IF - If for instance 44 are the 11 and 12 digits in from the right then these are replaced, but an other occurrences of 44, for instance at digits 6 and 7 are ignored? Is that an argument / logic that Excel can understand and work with?

    My husband is going to be SO relieved when I finally step away from the computer this evening ...

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    Iíve coloured the outer nest. If you look at each one inside it, they all follow the same pattern and syntax of the SUBSTITUTE function.

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(MID(M1,FIND(":",M1)+1,99)="",MID(L1,FIND(":",L1)+1,99),MID(M1,FIND(":",M1)+1,99))),"+44",""),"-","")," ",""),"0(44)","")

    The TEXT function is then wrapped around the outside of the whole lot:

    =TEXT(... ,"0##########")

    The ellipsis represents the formula around which you are wrapping the TEXT function.

    Iíll have another look at the 44 issue tomorrow. Is there anything else that needs taking into account?

  9. #9
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Thanks Ali GW - I'll try and get my head round it. I have made progress since our last exchange and managed to work out how I added another substitute to the string and have since played around with replacing "e: 44" [that should work for both the phone and mobile fields] with "e: " but I'm still coming unstuck ......I'll try again with your above information. Many thanks for your help today - I have made great strides even though there are one or two things still eluding me. I don't believe there is anything else that needs takign into account. Night night sleep well

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    You, too!

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

    Re: Nesting formula and formatting cells as text not numbers

    Another approach:
    Eliminate any "-" and " " inside, then get 10 digit from right
    F1:
    ="0"&RIGHT(SUBSTITUTE(SUBSTITUTE(L1,"-","")," ",""),10)
    G1:
    For mobile:
    =IFERROR("0"&(RIGHT(SUBSTITUTE(SUBSTITUTE(M1,"-","")," ",""),10)+0),F1)

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    Quang's suggestion seems to work well. If you want it all in one formula, do it like this:

    =IFERROR("0"&(RIGHT(SUBSTITUTE(SUBSTITUTE(M1,"-","")," ",""),10)+0),"0"&RIGHT(SUBSTITUTE(SUBSTITUTE(L1,"-","")," ",""),10))

  13. #13
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Hi Ali GW - I've just tried this and it works brilliantly. Certainly good enough for what I need - there's always going to be the odd glitch because my starting point data is being submitted direct from contacts and when I've just tried your wonderful formula on a small sample of data I see that someone has just entered the landline where the mobile should be and vice versa meaning that I end up with the landline number in my captured data but I think I have to be realistic and accept that I have now got a very good system for the majority of the data that I want to filter (more than good enough though it is my nature to just keep nagging and nagging away at an issue until it is "perfect"). Many, many thanks again

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    You need to thank Quang as well.

    What you SHOULD be doing, really, is data validation at the collation point, if it's being done via an Excel workbook. This way you can get the numbers (a) in the right columns and (b) in the right format. What you are doing with this data cleansing is allowing the tail to wag the dog.

  15. #15
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Hi - I have already (of course) thanked Bebo 021999 (Quang) [ though now can't see it so might have to do that again ...].

    Your point about is being a data cleansing issue couldn't be more accurate unfortunately but it is not within my power to alter that . I get my data as a word document originally (with masses of unwanted data), I then convert it into a table and from there into Excel - I have tried various methods of reaching the end point that is asked of me and I think I am now at a point where it is as good as it gets. Its a case of making the best of what is being asked of me and now, thanks to you, I can speedily - and accurately- do that

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    OK - you hadn't thanked him here in the thread, and I have no way of seeing any comments you might have sent him privately - sorry. I was worried that you had missed his contribution.

    You might want to consider looking into PowerQuery - it's part of the latest versions of Excel, but you can get it from MS as a free download for Excel 2010. That might help you to cleanse your data whilst importing it. It would be interesting to see the format in which you receive it.

    Anyway, glad to have helped.

  17. #17
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Hi Bebo - I replied about an hour ago but now can't see that reply so just in case it has got lost in the system somewhere ......many thanks for your suggestion which had occurred to me too (eventually) but I just didn't know how to do it. Ali GW incorporated in her formula for me and I am now using it in my work and it is making the processes I need to do very much more efficient. So thanks to both you and Ali GW - so very much appreciated.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    Most likely you just didn't hit save before navigating away from the page!

  19. #19
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Thanks Ali GW - I'm glad you mentioned thanking the other commentator because if you hadn't my thanks might not have reached them - thanks have now been repeated in that direction. My Excel version is SO, SO ancient (2010) but I need to treat myself to a new computer so they will doubtless come as a package and I'll look into Power Query then too if I haven't got round to a free download before then.

    You have helped tremendously - when I get the time I am going to persist in trying to replicate the formula myself - last night's efforts weren't entirely straightforward as some part so the formula seemed to be accepted and other parts just gave my error messages ...

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    Error messages often occur because you have made errors with syntax: pay particular attention to commas and parenthesis, as one bracket short could easily trigger problems.

    A new PC will come with a trial version of Microsoft 365, probably. I have been using the subscription version for a few years now - I would never go back to a standalone version.

  21. #21
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Hi - that was my starting point before I reached this forum - syntax, commas and parenthisis .... how do I format / enter fformual / multiple functions?

    As a complete beginner (well as good as over and above the basic functions ...) I didn't ....and to a large extent still don't .....know how to enter multiple functions / formula. I can't find the right advice via the internet but someone will say, for instance, use this or that function - I get that / can do that but even then it took me a while to get to the point of knowing that when I had typed it a function I 'tabbed' on it (in my version of excel) to get Excel to accept into my worksheet and from there I realised that if I took my cursor to the beginning of an existing function / formula and entered in, say, substitute, 'tabbed' on it , then it would accept and start that function. About that same time that I twigged about the screen prompts, asking "text, old text, new text etc ....." I was /am starting from that level with functions. I know Microsoft word, PowerPoint etc EXTREMELY well and am comfortable with the BITS of excel that I have needed to use over the years .... just very new to functions / formula other than SUM, etc. So when you effectively said (just) ...."then wrap it all in a TEXT function: .. " hahaha - t was like telling me to fly to the moon. I had no idea how I just did that. So, still a learning curve for me and even though you did the formula for me (again so many thanks) I learned a lot ....and will continue to do so as and when time allows. I think finding the right tutorials aimed at functions (including the VERY basics) will be key. I'm not thick (thought felt it at times over the weekend) and consider myself very computer literate but, as has become apparent, its all relative. I master the basic and from there I soon will be flying ...

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: Nesting formula and formatting cells as text not numbers

    I am not sure what you mean by the question - sorry. However, if you click on existing formulae in the formula bar, you will see that Excel colour codes the constituent parts, so this can be a useful way to study the construction of complex and nested formulae. Itís just a case of keep plugging at it, really.
    Last edited by AliGW; 08-04-2020 at 01:14 AM.

  23. #23
    Registered User
    Join Date
    08-02-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    11

    Re: Nesting formula and formatting cells as text not numbers

    Hi - sorry for slow response but v busy day. Not a problem - I realise that, and its not something that you need to concern yourself with - you have more than helped already. Because of your help I am now able to carry out the things i need to do very much more efficiently - allowing me time to look further into functions and formulas at leisure at some point in the future. As you say - and as I intend - I will just keep plugging away.

+ 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] Formatting cells with Text formula
    By csnyder10 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2017, 01:23 PM
  2. Formula to remove some text and numbers from cells
    By milkychips in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-22-2016, 04:29 PM
  3. Conditional Formatting (if MULTIPLE cells contain ANY text & not numbers)
    By Biscuit17 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-20-2016, 09:01 PM
  4. Replies: 4
    Last Post: 02-26-2015, 06:10 AM
  5. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  6. [SOLVED] Formula to Sum numbers depending on text in other cells
    By ExcelGal in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-11-2013, 10:30 AM
  7. [SOLVED] Vlookup formula - return only numbers for cells containing text and numbers
    By Andrew E Smith in forum Excel General
    Replies: 11
    Last Post: 07-03-2012, 06:07 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