Excel 2010-convert text to value
When I download my bank statement in .csv format it displays all information in comma delimited format but in ONE column i.e
20131108,BONUS INTEREST ,1.66,19517.52,
I need info in excel format columns but when I use data-text to columns it display all info correctly in right colums except the values are displayed as text and not values
I am using office 2010
Any suggestions will be appreciated
thanks
ramsee
Hi
probably your entire sheet is formatted as text.
Ctrl+A and format as General before Text to columns
Thanks for trying...i did what you suggested but still not converting to value...Originally Posted by Pepe Le Mokko
Thanks for trying. I did what you suggested but still not converting to value
Not sure what number format is used in SA but try this formula
Assuming the values in C1
=--SUBSTITUTE(C1,".",",")
Last edited by AlKey; 01-14-2014 at 01:49 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
Thanks but i was hoping for a simpler method.
Hi and welcome to the forumWaar omtrent in SA?
If you want to do this with forumlas, try these, assuming your data starts in A1, down...
B1=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,2,7))
C1=TRIM(MID(A1,FIND(",",A1,1)+1,FIND("xx",SUBSTITUTE(A1,",","xx",2),1)-FIND(",",A1,1)-1))
D1=MID(A1,FIND("xx",SUBSTITUTE(A1,",","xx",2),1)+1,FIND("xx",SUBSTITUTE(A1,",","xx",3),1)-FIND("xx",SUBSTITUTE(A1,",","xx",2),1)-1)*1
E1=MID(A1,FIND("xx",SUBSTITUTE(A1,",","xx",3),1)+1,FIND("xx",SUBSTITUTE(A1,",","xx",4),1)-FIND("xx",SUBSTITUTE(A1,",","xx",3),1)-1)*1
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.. I am in Pretoria. I was hoping there is a simpler way to do it. Is it possible that my Excel 2010 is not working properly? Your assistance is appreciated..Jerry ramsee at xxxxxxxx.xx.xxxHi and welcome to the forum
If you want to do this with forumlas, try these, assuming your data starts in A1, down...
B1=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,2,7))
C1=TRIM(MID(A1,FIND(",",A1,1)+1,FIND("xx",SUBSTITUTE(A1,",","xx",2),1)-FIND(",",A1,1)-1))
D1=MID(A1,FIND("xx",SUBSTITUTE(A1,",","xx",2),1)+1,FIND("xx",SUBSTITUTE(A1,",","xx",3),1)-FIND("xx",SUBSTITUTE(A1,",","xx",2),1)-1)*1
E1=MID(A1,FIND("xx",SUBSTITUTE(A1,",","xx",3),1)+1,FIND("xx",SUBSTITUTE(A1,",","xx",4),1)-FIND("xx",SUBSTITUTE(A1,",","xx",3),1)-1)*1
Last edited by FDibbins; 01-15-2014 at 03:15 AM. Reason: Email address removed
Thanks but i was hoping for a simpler method.
When I used T2C, the 1, 3rd and 4th parts all converted to values/numbers. Is that not happening for you?
Thanks again...It does not convert to value. Any chance i could sent you the .csv file. Perhaps easier to check
sure
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Thanks again..herewith file. hope you received it. firts time i am doing it..ramsee
Thanks. I just clicked on the file here and it opened in excel
Apart from ACB part (2nd column), all the rest come in as values...
ACB DEBIT:EXTERNAL ( 14.50 ) ADT PTA PTSMI01495ADTA041825 ;;; ACB DEBIT:EXTERNAL ( 14.50 ) SABCSAUKTV 111294245 OCT 13 ;;; ACB DEBIT:EXTERNAL ( 14.50 ) OUTSURANCE OT16285706/41 ;;; ACB DEBIT:EXTERNAL ( 14.50 ) VOXCONS R843355 ;;; NOTIFIC FEE SMS NOTIFYME ( 3.00 ) 6 SMS KENNISGEWINGS ;;;
F:I I used =ISNUMBER(A3) copied down and across, to test.
What do you get when pull the data in?
I still get values....The amounts show as numerical values but try and add the two values and you will get -400.14 92757.12 #VALUE!
Nope, when I use =C3+D3 I get 92356.98
Thank you very much for your time in trying to assist me. Much appreciated
Then I guess my excel 2010 is corrupted?
Take a look at the attached and see how it compares to your file?
your file is excellent...kindly detail exact steps you have taken to convert my file to yours. Maybe i am using wrong steps
i took following steps
format sheet to general
then data text to column
delimited,tab, comma next general, finish
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks