LinkBack URL
About LinkBacks
how can i access a char in a string using vba code?? i tried using
char = left((right(word), i),1)
but it doesnt work
thanks in advance!
How to access a char from a string? vba
how can i access a char in a string using vba code?? i tried using
char = left((right(word), i),1)
but it doesnt work
thanks in advance!
Suggest you post an example of what you are trying to do specifically. Your explanation does not provide enough detail and my crystal ball fell on the floor yesterday and shattered.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
ok for example i have something like 1,234,567,89 and i would like to access the 9 .. then the 8 then the comma then the 7 then the 6 then .. so on
store them one at a time in the value of char
The main macro that i am making converts a string of numbers from text so that excel can interprate it as a number
deleted as the response was not correct.
http://msdn.microsoft.com/en-us/libr.../ms233783.aspx
i tried this as well but it doesn't seem to work in excel
Is 1,234,567,89 the string you want to convert to a number?
If it is then you could probably do it without going through each character one by one.
What should the result be, 123456789 or 1234567.89?
PS That link is for VB.NET.
If posting code please use code tags, see here.
There are two possibilities 1.234.567,89 becomes 1234567.89 and 1,234,567.89 is also 1234567.89
yes the 1,234,567,89 is the string i want converted to a number ..
i am trying to do it for every number possible .. not only those .. so the number 15,65413551 should be 15.65413551
Please Login or Register to view this content.
there is the code i made so that it could work
Don't you mean at least 3 possibilities?
1.234.567,89
1,234,567.89
1,234,567,89
1.234.567.89 perhaps?
yes but the program that those are being extracted from (SAP) don't have the other two .. and there are still some bugs in my code ..
What 2 does SAP have?
Your original post was about the string, 1,234,567,89.
Then in a later post you post 3 more example strings, 1.234.567,89, 1,234,567.89 and 15,65413551.
I'm pretty sure if you can clarify what you have then a solution that doesn't involve going through every character could be found.
By the way, why are you getting this data in different number formats?
sap technically has 3 but one is recongnized by excel as a number right away witch is 1 234 567,89
and the two that don't work are 1,234,567.89
and 1.234.567,89
sorry about the confusion ..
norie .. sap is a pice of crap software .. is the best answer i can give you
I've used SAP before and I can't recall it having different formats like that.
Actually I think there was some (personal?) setting you could change to control numebr formats.
Could you attach a workbook with some sample data that shows examples of all the different formats?
yes it is a personal setting, but many people use sap here and if one person extracts with a different setting then i have to change all the numbers from text to numbers especially when we get to the thousands.
sap errors2.xlsx
I'll have a look at the file, but a couple of quick questions.
When you extract data how are you doing it?
Also, does the extracted data have always have a mixture of date formats or does each extract always have the same date format?
We usually extract it with the extract to excel button .
Its a good question I still don't know the exact answer to that since i am not the one who does the extracts.
I was able to use Data>Text to columns... in your sample workbook to conver to numeric values.
All I did was change round the decimal and thousand operators on the last step by going to Advanced...
I don't know if that will work for all the formats you are encountering but it does with your sample data.
Not sure but, is this how you wanted?
If not, I need to see Before/After in the workbook.
Please Login or Register to view this content.
to access the a character in a string you can use len() with for loop to get the position of each character in that string
Please Login or Register to view this content.
slx that didn't work in my case ... it doesn't give me the results that i want ..sap errors2.xlsx jindon look on sheet 2 for the desired answer
i made a mistake in the answer sheet ... but i believe i just realized something
Maybe:
Please Login or Register to view this content.
Here's it is going right to left.
To fix your problem u need to have clear rules.Please Login or Register to view this content.
Starting with replace all "," with ".". Then count the ".", if there is only 1, then remove it If there more than 1, then use the first "." from the right. If there is none then leave it alone.
Please Login or Register to view this content.
nice try but no ciggar .. yudlugar i get an infinite loop and slx it just doesn't work it does nothing ..
Have you actually tried my code?
jindon it works just that for the first one it should be 3000 ml and for the second it should be 6000 ml instead of 3 ml and 6 ml sorry..
I see,
This should do.
Please Login or Register to view this content.
Or maybe simply
Please Login or Register to view this content.
Roomie, it works, you have to be on sheet1 for it work since i didn't reference worksheets. The answer gets put in column "L" as demonstration.
?Please Login or Register to view this content.
jindon could we have the range change with the active cell function .. like change the values but only in the cell that is selected.. .... i don't know if i'm speaking Japanese
Just change
toPlease Login or Register to view this content.
Please 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