Reply With QuoteLionel. Can you make another column? (H)?
TopicToaster
Hello All Excel Spreadsheet Formula Users,
Just to keep Incisor's Excel thread running.
Is there a What-If analysis, or some similar function, that can isolate the last four numbers of a column that should have featured in a spreadsheet as a separate column that identifies a post code. For example - find examples where the post code 4670 appears in a 70 page spreadsheet.
Here is a sample of what the author wrote Column "G" as:
23 Isaac Street, Deeragun, QLD 4818
6 Narabang Way, Belrose, NSW 2085
60 Margaret Street, Level 24, Sydney, NSW 2000
47 Bourbong Street, Bundaberg, QLD 4670
55 Fulham Road, Pimlico, QLD 4812
106 Cunningham Street, Dalby, QLD 4405
43 Gregory Street, Mackay, QLD 4740
78 Beischer Street, Bendigo, VIC 3550
Po Box 4546, Kirwan, QLD 4817
How could I write a formula that sorts the 4670 entries and their related data in the same row cells out for 4670?
The technique I have been using to date has been highlight column G and perform a Find function to identify each occurrence of 4670. This is a laborious task that will not get any easier as the file is updated every now and then.
I tried a "Custom Sort and Filter" however, the mixture of numbers and letters in the cell prevented it. The author is also inconsistent with their use of commas so "Comma delineation" does not work either.
I would appreciate assistance - thank you.
Kind Regards
Lionel
Lionel. Can you make another column? (H)?
Wizard
SupporterThat would work, create a helper colum next to your data: Use the formula RIGHT and specifiy 4 characters - this will extract the last 4 digits from the right (providing that the last 4 digits are always a postcode:Originally Posted by Tombie
I just copied one of your address's and pasted into cell B3, in cell C3 I entered the forumula =RIGHT(B3,4). That will pull the postcode into your helper column
Capture.PNG
Shane
2005 D3 TDV6 loaded to the brim with 4 kids!
http://www.aulro.com/afvb/members-rides/220914-too-many-defender-write-ups-here-time-d3.html
TopicToaster
Hello Shane,
Well that worked a treat Shane. Thank you very much for sharing your knowledge. =RIGHT(G3,4)
I also just found how to copy this formula from Cell H3 to H4953 (H3:H4953); and then go Control + D to fill the whole column with post codes.
I could then do a Custom Filter for 4670 and get rid of 69 pages of detritus
It is a shame that a certain government department could not just expend a little more effort and have the last two columns labelled "Suburb" and "Post Code" so that no one had to do =RIGHT(G3,4) in the first place!
However, that would be just too damn user-friendly!
Kind Regards
Lionel
Glad that sorted it out Lionel..
I live in an excel space!!
Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!
Wizard
Holy. Crap. That thing is monstrous!Glad that sorted it out Lionel..
I live in an excel space!!
Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!
Master
Toombie, I'm an Excel nerd, but I've got to ask, have you heard of Access?
I was thinking Access for the OP, and he's only got 5,000 records!
That spreadsheet was fed from several ADbs
But I hear what you’re saying!
The Challenge was the ever changing outputs and inputs.
Although Nowadays I play in a much broader space, from Excel reports to Isolation Systems and Confined Space Rescue Plans, through to hands on construction, trouble shooting and commissioning of new plants....
Master
SubscriberI can’t see the bottom left corner but do you have coffee for each monitor?Glad that sorted it out Lionel..
I live in an excel space!!
Yes it’s one spread sheet, yes it’s 60+ tabs, over 2000 lines per tab and all interconnected!
All the best,
Laurie
2019 sd6 SE
2012 SDV6 SE (Son stole it from me)
Major Part of the Heart and Soul of AULRO
SubscriberWhen people come to you assign for information,. can you say
"Don't bother me, read it for yourself ! It's right there plain as day in cell 1985674 GZ" ?
Anyway there is only one formula that anyone here needs to know.
The number of Land Rovers you NEED is n=n+1 (where 'n' is the number you already have)
It's not broken. It's "Carbon Neutral".
gone
1993 Defender 110 ute "Doris"
1994 Range Rover Vogue LSE "The Luxo-Barge"
1994 Defender 130 HCPU "Rolly"
1996 Discovery 1
current
1995 Defender 130 HCPU and Suzuki GSX1400
Posting Permissions
| Search AULRO.com ONLY! |
Search All the Web! |
|---|
|
|
|
Bookmarks