• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data in excel change proper format

A

Abhijeet

Active Member
Hi

I have data in excel when 358E154 this data enter into cell then this convert like this 3.58E+156 can any one tell how to avoid this or if this type of value how to convert again into like this 358E154 please tell me
 
i tried with this method format text but when macro copy paste data then this values are converted so please tell me how to this type value converted into original value
 
Abhijeet said:
i tried with this method format text but when macro copy paste data then this values are converted so please tell me how to this type value converted into original value
Click to expand...

Use Z358E154.

or VBA like as below

Code: 
    Range("G65").Copy
    Range("I63").PasteSpecial Paste:=xlPasteValues
 
If Use Z358E154. then same value show how to replace that to 358E154
if text format cell replace the Z then gives 3.58E+156 value
 
Macro part from MS access data base this macro pull data from table so i am not aware where i need to change in code because in that macro SQL method is also use so i am not sure
 
First format target cell as Text. Then paste in the value.

EDIT: If using ADO or other SQL method, you can either set IMEX:=1 in connection string, or define data type in SQL Statement.
 
Abhijeet said:
Macro part from MS access data base this macro pull data from table so i am not aware where i need to change in code because in that macro SQL method is also use so i am not sure
Click to expand...

Why not you are used to share the all info in very first post!!

Share the complete details with code/input/output if still looking for the solution!
 
Ok that macro part i will check but if any method like this to replace "." then "+" and after + sign number -2 from that value means from this value 3.58E+156 358E154 my original value come

Please tell me how to do this
Code: 
Sub ChgInfo()
   
    Dim WS              As Worksheet
    Dim Search          As String
    Dim Replacement    As String
    Dim Prompt          As String
    Dim Title          As String
    Dim MatchCase      As Boolean
   
    Prompt = "What is the original value you want to replace?"
    Title = "Search Value Input"
    Search = InputBox(Prompt, Title)
   
    Prompt = "What is the replacement value?"
    Title = "Search Value Input"
    Replacement = InputBox(Prompt, Title)
   
    For Each WS In Worksheets
        WS.Cells.Replace What:=Search, Replacement:=Replacement, _
        LookAt:=xlPart, MatchCase:=False
    Next
   
End Sub
 
Deepak said:
Why not you are used to share the all info in very first post!!

Share the complete details with code/input/output if still looking for the solution!
Click to expand...
i do not have code with me now so i can not share that and my question is when 3.58E+156 this type of value how to convert like after + sign number 156 minus 2 then 154 comes then original value 358E154
 
:rolleyes:

th
 
This one formula i tried but in this how to minus 2 from last 3 numbers can you please tell me

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"(","_"),"^","_"),"+"," ")
 
You must log in or register to reply here.
Back
Top