Quick Excel Help Page 7

    Next Last
  • MrWorf 22 Sep 2010 11:59:56 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Awesome! Cheers, worked a treat
  • tenofspades 4 Oct 2010 17:43:47 1,802 posts
    Seen 4 weeks ago
    Registered 15 years ago
    Hello Excel Gurus. In this following formula:

    =VLOOKUP($A6,tableDetails,COLUMN(),FALSE)

    What does $A6 mean- I can guess the rest but has this guy made a column into a string or something?
  • mrpon 4 Oct 2010 18:09:14 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    The dollar sign means a fixed cell in a formula.
  • Deleted user 4 October 2010 18:10:30
    And more specifically, he's fixed the column so if the cell is copied down or across it will still refer to column A.
  • tenofspades 4 Oct 2010 18:24:29 1,802 posts
    Seen 4 weeks ago
    Registered 15 years ago
    ah thanks :)
  • Deleted user 25 November 2010 10:54:52
    Post deleted
  • mrpon 14 May 2018 16:23:48 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    I've got a formula driven cell that ends up with 9/59 in it. I want to take this cell value and actually compute it in another cell ie: 0.15

    I know how to do it by using the other values etc.. was just wondering if there was a =RunAsFormula type command.

    Enjoy.
  • X201 14 May 2018 17:03:50 22,082 posts
    Seen 3 hours ago
    Registered 15 years ago
    There used to be a trick to do this, but apparently it's been removed.

    Only way I've managed so far is to do a string operation and put the 9 and 59 in separate cells and then calc them.
  • Your-Mother 14 May 2018 17:37:30 8,011 posts
    Seen 13 minutes ago
    Registered 5 years ago
    Select a blank cell you want the result in, go to Formula/Define Name

    Name it whatever you want (eg runasfunction), in Refers To, type =EVALUATE($yourcellwiththefirstresult)

    In the blank cell type =runasfunction (or whatever you called it).

    Edited by Your-Mother at 17:41:44 14-05-2018
  • mrpon 14 May 2018 18:24:47 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    I don't care what anyone says about your Mum, erm....he's great!!

    Good effort fella, works a treat!!
  • Nexus_6 3 Apr 2020 15:06:53 6,140 posts
    Seen 3 hours ago
    Registered 17 years ago
    Guys I need some Excel help. I have a bar graph that I have colour-coded in primary colours to symbolise different nations. No problem - but I need to find out how to add little skulls with their eyes crossed out at the top of each individual bar - there doesn't appear to be a button for this and the online help is next to useless.

    If anyone could provide a sketch for the skulls also this would be really appreciated.
  • Your-Mother 3 Apr 2020 15:09:17 8,011 posts
    Seen 13 minutes ago
    Registered 5 years ago
    The skulls could be modeled on Tonka’s face and when you click on them they go “WAKE UP SHEEPLE”
  • Nexus_6 3 Apr 2020 15:10:11 6,140 posts
    Seen 3 hours ago
    Registered 17 years ago
    Yes I like where this is going.
    Maybe a hyperlink to a red-pill website?

    lets keep the ideas-pot boiling
  • X201 3 Apr 2020 16:57:21 22,082 posts
    Seen 3 hours ago
    Registered 15 years ago
    @Nexus_6
    Not exactly what you want, but try this.


    Create bar chart
    In separate area of spreadsheet insert image (use the skull icon for quickness)
    Copy skull, select bars (data series) in chart and paste the skull onto the bars
    In Format Data Series, choose your data series.
    Then click Format & Line (Paint Can)
    Scroll down and select Stack and scale with units/picture
    Set the attached box to 30 for starters
  • X201 3 Apr 2020 16:58:46 22,082 posts
    Seen 3 hours ago
    Registered 15 years ago
    @Nexus_6 And you can get the skull icon from Excel

    from insert illustrations > icons
  • mrpon 3 Apr 2020 16:59:17 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    :rolleyes:
  • X201 3 Apr 2020 19:09:56 22,082 posts
    Seen 3 hours ago
    Registered 15 years ago
    What?
  • Nexus_6 3 Apr 2020 19:13:04 6,140 posts
    Seen 3 hours ago
    Registered 17 years ago
    mrpon wrote:
    :rolleyes:
  • Your-Mother 4 Apr 2020 00:06:18 8,011 posts
    Seen 13 minutes ago
    Registered 5 years ago
    ☠️☠️☠️☠️☠️☠️
  • Load_2.0 16 Jun 2021 12:31:22 33,520 posts
    Seen 5 hours ago
    Registered 18 years ago
    Pretty positive I know the answer is No Dumbass! But....

    I have a 2000+ cells with address details. I need to split them into street address, post code and country.

    Problem is there is no delimiter between most of the city and postcode. For example

    LondonSW11WS

    Without a semicolon or space I'm fucked right?
  • mrpon 16 Jun 2021 13:09:53 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    Assuming A1 contains that example, then something like this could work:

    =RIGHT(A1,LEN(A1)-SEARCH("S",A1,2)+1)
    However!! That's only searching on S and would need support for other letters, you could mirror that formula across other columns for other letters. It also assumes there aren't any other capital letters after the first one!!

    Enjoy!
  • SamNunn92 16 Jun 2021 14:13:17 938 posts
    Seen 2 hours ago
    Registered 10 years ago
    The other thing you could try is (assuming that the postcode is always upper case, the postcode is always at the end of the string and there aren't any spaces in the postcodes) searching from the right hand side of the string by a certain index and then checking if the substring contains any lower case letters. If it does, repeat with a shorter index until you find the first length where it's the same, and then return that substring as the postcode.

    =IF(EXACT(UPPER(RIGHT(A4, 7)), (RIGHT(A4, 7))), RIGHT(A4, 7), IF(EXACT(UPPER(RIGHT(A4, 6)), (RIGHT(A4, 6))), RIGHT(A4, 6), IF(EXACT(UPPER(RIGHT(A4, 5)), (RIGHT(A4, 5))), RIGHT(A4, 5), "not found")))
  • mrpon 16 Jun 2021 15:17:31 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    That's lovely Sam! It's what I was trying to achieve with my hamfisted solution!
  • MMMarmite 16 Jun 2021 15:27:21 1,659 posts
    Seen 4 hours ago
    Registered 15 years ago
    If they were all UK addresses you could do something like, find the first number then take anything left of that -2 as the city and anything right +2 as the postcode - since all UK postcodes start with 2 letters and then a number it's a common pattern you can use.

    For quite a few other countries you may be able to just search for the first number and split on that.

    This all assumes you've already split out the other address details containing numbers like house number, street, etc...
  • Nazo 16 Jun 2021 15:29:56 1,902 posts
    Seen 3 hours ago
    Registered 12 years ago
    MMMarmite wrote:
    since all UK postcodes start with 2 letters and then a number
    No they don't, some start with 1 letter.
  • MMMarmite 16 Jun 2021 15:40:07 1,659 posts
    Seen 4 hours ago
    Registered 15 years ago
    @Nazo Fair point, I'm a dumbass and live in the sticks so I've never used one with a single letter ;)

    That does however mean that unless the postcodes with letters are all uppercased it'll be quite difficult to split them.

    Edited by MMMarmite at 15:44:12 16-06-2021
  • askew 16 Jun 2021 16:00:17 24,005 posts
    Seen 2 hours ago
    Registered 16 years ago
    Urgh, which deviant supplied that data?!
  • Graxlar_v3 16 Jun 2021 16:16:44 11,224 posts
    Seen 2 hours ago
    Registered 4 years ago
    Load_2.0 wrote:
    Pretty positive I know the answer is No Dumbass! But....

    I have a 2000+ cells with address details. I need to split them into street address, post code and country.

    Problem is there is no delimiter between most of the city and postcode. For example

    LondonSW11WS

    Without a semicolon or space I'm fucked right?
    Send it back and demand a refund.
  • Load_2.0 16 Jun 2021 17:14:30 33,520 posts
    Seen 5 hours ago
    Registered 18 years ago
    Thanks, I really appreciate the solutions.

    I've set aside an afternoon to fix it so will give the above a try.

    I'd been chasing this info for weeks so was delighted to get it, that joy was short-lived.
  • SamNunn92 16 Jun 2021 17:14:40 938 posts
    Seen 2 hours ago
    Registered 10 years ago
    mrpon wrote:
    That's lovely Sam! It's what I was trying to achieve with my hamfisted solution!
    Thanks! we had a very similar issue at work a while back so had to use something like the above for that.
  • Next Last
Sign in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.