I have attached an example spreadsheet.
Here is what I am trying to do. I have a column with 5 digit extensions and a column for 11 digit extensions. These extensions are in two different ranges. 5XXXX and 7XXXX. I need a formula that will check the values in the column and if it finds a number that begins with 5XXXX then transform it into 1312555XXXX or if it finds a 7XXXX transform it into 1312777XXXX.
I am sure this is easy to accomplish.
Thanks!
See attached.
try
=IF(LEFT(B2,1)="5","131255"&B2,IF(LEFT(B2,1)="7","131277" &B2,""))
Using your posted workbook
try this formula, copied down:
Is that something you can work with?
Please Login or Register to view this content.
One more
=INDEX({"131255","131277"}&B2,IF(LEFT(B2)="5",1,IF(LEFT(B2)="7",2,"")))
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
I just got back to my work station, I will try each and let you know which is most efficient and scalable, should I need to include additional ranges.
Will report back soon! Thanks!
Ron Coderre is the winner!
I find this formula easiest on the eyes and easiest to scale, though I am quite impressed with the different ways to arrive at a solution. There were a couple of different angles I did not consider.
=CHOOSE(MATCH(LEFT(B2,1),{"A","B"},0),"X","Y")&B2 is a great template to work from. Is there an upper limit to the search criteria within the squiggly brackets?
Ron, this is the first time I am using the CHOOSE function. Neat stuff.
Rep give, appreaction for all.
Thank you again, I'm sure I'll have another post soon.
One last question
Is is possible to wrap this in an IFERROR to display a black cell instead of #N/A in the case that the source cell is blank
I have been tinkering around with this... but no luck.
=IFERROR(CHOOSE(MATCH(LEFT(B2,1),{"A","B"},0),"X","Y")&B2 "")
Any advice?
EDIT: This seems to work, is it an elegant solution though? Is that in the eye of the beholder? =CHOOSE(MATCH(LEFT(B2,1),{"","A","B"},0),"","X","Y")&B2
Thanks!
Last edited by xDimitrius; 09-22-2015 at 12:11 PM.
I think this variation catches all errors...
but I don't know if you gain anything by matching a blank string, so this might work just as efficiently:Please Login or Register to view this content.
Please Login or Register to view this content.
=IFERROR(CHOOSE(MATCH(LEFT(B2,1),{"","A","B"},0),"","X","Y")&B2,"")
I've settled on this forumula.
Topic marked Solved.
Thanks again for all of the contributors to this thread.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks