Hi,
Given attached sheet where I need the alphanumeric number or number in column F to be pasted in the adjacent new column. Ideally, this number comes before a hyphen
Thanks
Crystal
Hi,
Given attached sheet where I need the alphanumeric number or number in column F to be pasted in the adjacent new column. Ideally, this number comes before a hyphen
Thanks
Crystal
Easy for cells like F2
=LEFT(F2,FIND("-",F2)-2)
But what do you want returned from much more complex cells, like F5?
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Crystal,
Welcome to the forum!
Sometimes there are more numbers in the F field, this will get the first one??
Put it in F2 and drag down or CTRL+SHIFT+ENDPlease Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hi Glenn,
I tried this formula and getting a circular reference warning. I pressed enter though and the value came up to zero,
In F5 cell the content that is needed is the numbers after "code" and code appears twice in these type of cells. I have to filter them from C - claim edits and medical necessity. Can u still assist me
Thanks
Crystal
Hi Guru,
I tried this formula and I am getting the same value which is the end cell that i end with, whatever 5-digit number is there in that cell, all the cells convert to this number, this is not what i want,
i want the number to be displayed as it is in that cell, can u assist me,
There are 2 types of cells, when i filter C column, the claim edits cells has a complex text and the needed info is after the word " code:
in the other type there are 5 digits either numerical or alpha numerical before the hypen that is needed
Hope I have made myself clear on this
Pl assist me.. thanks crystal
Hi Crystal,
It's the same problem with code:Hit the F9 button to make the formula calculate - no need to change the formula, you get the same results whether you use description or codePlease Login or Register to view this content.
Hi there. Providing that you always want the digits after the first instance of "code:", or that it is ALWAYS a 5-digit alphanumeric that you want at the start f the cell - then xladept's solution will work perfectly. I don't see why you got a circular error, so I've attcahed your sheet with xladept's solution to show you how it works.
Hi Guru,
probably I am not getting it right, pl see belowattached, have put in formulas till row 24, and it is not picking the correct number
You have to put the formula in the next available column (column G) and drag it down. It will not work if you put it in the same column as the data you're trying to select from.
Did you open the attachment to post number 7?
Last edited by Glenn Kennedy; 06-06-2014 at 02:14 PM.
I see what I did:I should have told you to put it in G2 - SorryPut it in F2 and drag down or CTRL+SHIFT+END
XLADEPT: HaHaHa... that makes me feel better - that someone as good as you sometimes makes silly mistakes...
Thank you Glen, that is what i want.....
You're welcome. If that's all, can you mark the thread as closed and (preferably) say thanks to all who helped by clicking the Add Reputation button at the foot of their post(s).
Hi Glenn, am back again, if you have looked into the attached sheet - the cell F5, in this cell, "code" is mentioned twice, the formula given has pulled out the 5-digit number after the first "code", now I want the second 5-digit number to be extracted in the adjacent column.
Thanks for your assistance in advance... Crystal
Hi Crystal - y0u forgot to attach the sheet!!
it is the same sheet that is attached in the previous posts,
Ok attached now
Here we go, Crystal. This one is horrible. So don't try too hard t figure out how it works, but it will work providing that the second code is always in the form "Code:" (minus the "s)... It woks for me - does it work where you are??
Formula:Please Login or Register to view this content.
Put it in H2 and copy down. let me know if it's OK...
Last edited by Glenn Kennedy; 06-08-2014 at 02:24 PM. Reason: I've attached the file again...
Thanks a bunch, this is what I needed, can i use the same forumula for my every month report, since the format is going to be the same... crystal
All being well, yes. If you run into problems, drop me a PM, in case I miss your post. I travel a lot & when on a plane, I forget the forum...
For now, if that's all, can you mark the thread as solved and (ideally) click the Add Reputation button at the foot of the post(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks