hi
in addition to earlier thread I have some cells with letters and numbers eg s123,st223,f12, how can I lose the leading letter or letters ,leaving just numbers,any ideas please
cheers colin
hi
in addition to earlier thread I have some cells with letters and numbers eg s123,st223,f12, how can I lose the leading letter or letters ,leaving just numbers,any ideas please
cheers colin
Last edited by cfinch100; 08-21-2014 at 05:13 PM.
Try this...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
hi fdibbins
where would put this my codes are in column "v",not up to speed with formulas
cheers colin
Try this one
assuming codes are in A1
Place formula in column V1 and copy down
=LOOKUP(1E+100,--RIGHT(A1,{1,2,3,4,5,6,7,8,9,0}))
A B 1s123 123 2st223 223 3f12 12
Last edited by AlKey; 08-21-2014 at 04:37 PM.
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
hi alkey
that's better thanks only problem I enter in cell and pull down but will only do 2 rows although formula enters in other rows. they say value
cheers colin
I suggest you upload a sample spreadsheet with about 10-15 rows of data so we could figure our a solution for you.
To upload a file click on Go Advanced at the bottom of your screen then click on PaperClip icon and Add Files, Select File, locate your file and click Open then click Upload and click Done
hi alkey
formula in w and is looking up column b
cheers colin
Please see attached file with your codes extracted in Column W
I used array formula
=NPV(-0.9,,IFERROR((MID(B4,256-COLUMN(A:IV),1)%),""))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
hi alkey
that's great many thanks,have not got the foggiest what it means at the minute
cheers colin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks