• 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.

Facing issue while removing ' from a value

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have data in column A. Number of rows might be 10, 100 or 1,000.
Each cell has value something as '130997109'. I need to remove ' form start and end of '130997109'. So the final value will be 130997109.

One problem is, not all the values will be with '. There will be some values as 130997109' or '130997109 (character ' at end, at start).

I tried using replace, but it does not work correctly.

Main issue is, when I am trying to replace '998993E96' this value with 998993E96.
It becomes 9.98993E+101. How do I resolve this.

Even if I am removing ' manually from this value, I get answer as 9.98993E+101.
Ideally it should be 998993E96.

Can anyone please help me in this.
 
Hi Sachin ,

What is the ultimate objective of this data processing ?

Your data also seems to have the space-like character with the ASCII code 160 ; removing this may also need to be done. Thus , as a first step , you can easily replace all apostrophe characters with this character ; this will not convert the resulting value to numeric exponential format.

Narayan
 
Hi @Deepak sir, thanks a lot for the help. However I am still facing the same issue.

I used both =TEXT(TRIM(SUBSTITUTE(A1,"'"," ")),"@") and =TEXT(SUBSTITUTE(A1,"'",""),"@") but getting 9.9899E+101 as output. Could you please help if you get time.

Good night, have a nice weekend. :)

@NARAYANK991 sir, thanks a lot for the help. Please give me 10-15 minutes. I will reply with details. Good night, have a nice weekend. :)
 
Hi @Deepak sir and @NARAYANK991 sir, I guess, I am able to resolve the issue. Please give me some time to re-confirm.

I have used below mentioned formula, base value in column A, $B$1 = ' (tricky, weird, trouble maker character).

B2 =LEFT(A2,1)
C2 =RIGHT(A2,1)
D2 =COUNTIF(B2:C2,$B$1)
E2 =LEN(A2)
F2 =E2-D2
G2 =MID(A2,D2,F2)


Thanks a lot. :)
 
@ThrottleWorks

For '998993E96' just use =SUBSTITUTE(A1,"'","")
=TEXT(string,"@") will convert it to general format and will consider it number.
Leave it as is and it will be returned as straight text format.
 
Hi @Deepak sir, I tried Narayan sir's suggestion. But I was getting space in output and was not able to remove the space even after using trim. Although, the problem of 9.98993E+101 vanished with his formula.

I was bit confused about ASCII Code 160 so I tried something different.

@Chihiro sir, thanks a lot for the help. Your formula is working perfectly. Have a nice weekend. :)

@NARAYANK991 sir, this processed data will be input for some other processing.
For that value needs to be in 9 characters and without ' value. Else it will not provide correct results. Thanks a lot.

 
You must log in or register to reply here.
Back
Top