Hi,
I've been tasked with formatting a very badly managed worksheet containing easting / northing's.
The type of formats used are (numbers are for show):
1. 291493 : 055473
2. (293692, 87898)
3. (383098, 203305)
4. Easting 287426 Northing 63057
5. EASTING 372410 NORTHING 166185
6. (E 316700, N 175502)
What I'd ideally like to do is pluck out the 1st 6 numbers (Easting) and then the last 6 numbers (Northing) and do this independently so I can have 2 different columns.
Thanks
Are the numbers: 1. 2. 3., etc, present in your cells?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
No, simply used them for show here to identify the different uses of how they've inserted the references before.
Sorry for the confusion.
OK. For the Eastings:
=LOOKUP(99^99,--(0&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)+1)))))
For the Northings:
=LOOKUP(99^99,--(0&MID(SUBSTITUTE(A1,B1,""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,B1,"")&1234567890)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,B1,""))+1)))))
This has "lost" the leading zero in 055473 Do you want the results formatted to give 6 characters?
If you do, simply select the cells and format as custom: 000000
Thanks
I'll have a little play around with it and get back to you in 20 mins if any problems
Using your posted example (removing the leading ordinals) in Col_A
B1: the ordinal of the number to pull.......1
D1: the ordinal of the number to pull.......2
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
pulls that number from the referenced cell
Please Login or Register to view this content.In your example, those formulas returns: 291493 and 55473Please Login or Register to view this content.
Copy C1 and paste into C2 and down as far as you need
Copy E1 and paste into E2 and down as far as you need
Be sure to extend the 1's and 2's in columns B and D
These are the results:
Is that something you can work with?
In B1 and copy down
Formula:
Please Login or Register to view this content.
in C1 and copy down
Formula:Please Login or Register to view this content.
***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
Format cells as General
291493 : 055473 (293692, 87898) (383098, 203305) Easting 287426 Northing 63057 EASTING 372410 NORTHING 166185 (E 316700, N 175502)
Sheet: Sheet1
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Another option
first 6
=MID(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&1234567890)),6)
second 6
=RIGHT(TRIM(SUBSTITUTE(A1,")","")),6)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
OK, Alkey, put me out of my misery.... How can that work??? Clearly, it DOES, but I don't see how NPV delivered this result.... Why -0.9?
LOL! I have no idea! I found this formula long time ago and I never used it before for the reason that I have no clue how it works.Originally Posted by Glenn Kennedy
At least you're honest. It's back to the tiny "evaluate formula" window for me....
OT, but yes, why don't they make that bigger???
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
LOL, let me know if you figure it out, Glenn!
I might have known that a "familiar face" here has written extensively on the subject:
http://excelxor.com/2014/11/01/extra...a-single-cell/
try:
1.I assumed first numeric string has 6 characters.Please Login or Register to view this content.
2.I assumed 8 places from right don't argue with places of first numeric string.(if so, can be changed to 7)Please Login or Register to view this content.
and after second numeric string is no more then 2 characters, and two character between numeric strings (too many posibilities)
both formulas are CSE entered.
result:
291493 055473
293692 87898
383098 203305
287426 63057
372410 166185
316700 175502
Last edited by sandy666; 12-30-2015 at 06:07 AM. Reason: second formula changed
This is why I don't post that formula, unless there's absolutely no other alternative. You need to really understand the way financial formulas work to even understand the explanation!
NPV is actually summing a series of PV calculations:
Hey,
I was having a play around with Glenn's formula and wondered how to deal with an extra formatting type i.e 323934,199510 currrently it'll almost do it by typing 323934199510 and if I had the space it'll do it perfectly.
One way... Eastings:
=LOOKUP(99^99,--(0&MID(SUBSTITUTE(A1,","," "),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,","," ")&1234567890)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,","," "))+1)))))
Northings:
=LOOKUP(99^99,--(0&MID(SUBSTITUTE(A1,B1,""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,B1,"")&1234567890)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,B1,""))+1)))))
But... if you like the quirky/offbeat/"How on earth did that work?" type of solution.... Alkey's effort works perfectly well with the additional format,too. having spent a bit of time yesterday looking at it, this format might be more efficient.
Eastings:
=LEFT(INT(NPV(-0.9,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)/10,""))),6)
Northings:
=RIGHT(INT(NPV(-0.9,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)/10,""))),6)
Both entered as an array.
Last edited by Glenn Kennedy; 12-30-2015 at 05:24 AM.
Thanks again, enjoyable read about that NPV formula, still haven't a clue but used it for the wow factor if anyone asks how it works :D
As long as they don't demand an explanation from you!!
Explanation: Black magic was used.
That will do it !!!!!
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Nominate for thread of the month.
Not to take anything away from the NPV formula...BUT..
we need to be aware that it pulls and concatenates ALL digits from the referenced text and it is limited to 15 significant digits.
Example:
A1: 291493 : 055473 : 1234
This formula could pull the left 6 digits (291493)
But this one couldn't pull the right 6 digits (731234)Please Login or Register to view this content.
The reason is that the base formula returns: 2914930554731250Please Login or Register to view this content.
Significant digits get pared off the right side.
While the formula I posted is certainly more verbose...it is capable of pulling any discrete series of digits (limited to 15 significant digits per series, of course).
Example:
A1: abc1234DEF567ghi891011JKLM121314op
numeric instances to pull
B1: 1
B2: 2
B3: 3
B4: 4
This array formula, copied down, returns the specified values
In that example, the formulas return:Please Login or Register to view this content.
Please Login or Register to view this content.
AlKey what mean (NPV(-0.9
NPV is Net Present Value. If you want to try to understand how it works, follow the link that I provided at Post 15. Good luck. You'll need it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks