LinkBack URL
About LinkBacks
Say I have a string "09800EBHR052708"
How would I take the first 5 characters and get a number out of them?
Say I have a string "09800EBHR052708"
How would I take the first 5 characters and get a number out of them?
Try:
=Left(A1,5)+0
where A1 contains original string.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If that string were in cell A5, then:
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
What does the +0 provide? Also, don't forget the CODE tags, I got infracted for that once already.Originally Posted by NBVC
The +0 converts a text string (which the Left function extracts) into an actual number.
You need the +0 to convert the String to a number. If you just use the formula, the result is actually treated as text and can lead to problems in data processing.
Hello no48,
Welcome to the Forum!
Are you looking for a formula or VBA solution to this problem? This forum is for VBA programming. In the future, if you need a formula solution then you should post your question in the Worksheet Functions forum.
Sincerely,
Leith Ross
Hi Leith;
I am looking for a VBA solution for it, I have got an array full of file names, and need to place links to the respective file in the box next to the one corresponding with said file.
Maybe I just assumed the OP picked the wrong forum...
Hello no48,
It appears you are using this number as part of a file name. Can you give us an example of what the completed string should look like?
Sincerely,
Leith Ross
Well, the file name would be 09800EBHR052708-001.jpg
From that I would want to get a numerical value of 9800 (ie. take the first 5 characters and convert to an integer, or any usable number value)
If the file were 79200EBHR052708-003.jpg I would want a numerical value of 79200, etc...
Hello no48,
This macro will check the cells in column "A" on the active sheet and place the 5 digit number in the cell to the right on the same row (column "B"). The VBA Val will convert a string up to the first non numeric convertible character. This code will work fine as long as your jpg file names all begin with a number.
Sincerely,Please Login or Register to view this content.
Leith Ross
Wouldn't this simple formula filled down to the bottom of the list have been easier?
=Left(A1,5)+0
A quick note:
I think Leith forgot to add one line of code, so it won't work. Amend his code to something like:
It seems that he had forgotten the With / End With linesPlease Login or Register to view this content.
Hello BigBas,
Thanks for catching that.
Sincerely,
Leith Ross
I tried BigBas code and it didn't give me the correct values. I attached my spreadsheet. I copied and pasted direct. Is it because I am on 2000?
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
Chemist:
Good catch. In correcting Leith's typo, I made one of my own. Try this code:
Note: We previously had rng.offset and it should have been cell.offsetPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks