display error

[jolinchew]

display error, I want to change column U, for example from 4.000002E13 to 40000165342013, pls see attached. how to do? why it display like ***2E13, how to prevent this.

also if I have cell "12345672013", how to make it by trail 2013 in the cell, I want it to be "1234567"

[excelledsoftware]

Originally Posted by jolinchew

display error, I want to change column U, for example from 4.000002E13 to 40000165342013, pls see attached. how to do? why it display like ***2E13, how to prevent this.

also if I have cell "12345672013", how to make it by trail 2013 in the cell, I want it to be "1234567"

This appears to be 2 issues so here are 2 solutions.

It appears that the first issue is dealing with what is called scientific notation. Besides converting the cell to text you could add a ' into the result cell. an example would be to place this formula in cell u1 ="'"&a1
the quotes are around the ' so it is " ' " without the spaces. This will show the whole number. If you need to do calculations with it calculate the value in A1 not U1. U1 is just so you can see the number.

Now the 2nd issue could be accomplished by the formula =right(a1,4)
This will take the last 4 characters of a string and display them. so 01234567892013 will show 2013. Again if you need to do calculation on this change the formula to =right(a1,4)+0

Let me know if this makes sense.

Thanks

[jolinchew]

No, I want to remove 2013, not to get 2013, eg. 345672013, I want 34567, not 2013.

if it is ABC2013, I want ABC, if it is ABCTW2013, I want ABCTW. it can have different length

The problem not resolved

[Pecoflyer]

Ctrl+H
replace what :2013
with: (leave blank)

Replace all

[jolinchew]

problem still not resolved, I have below cases:

2sw2013cf2013, I want it to be 2sw2013cf, not 2swcf.

I just want to trim '2013' at the end of phase, any idea?

[BobBridges]

Jolinchew, are you saying you want—well, let me rephrase it and you tell me whether it's right:

I have a string that is at least four characters long. Somewhere in that string is the substring "2013". Wherever that exists, I want the string to be truncated just before that. For instance:

"1234567892103": Display "123456789"
"abc7892013zyx321": Display "abc789" (dropping "2013" and everything that comes after it)
"Happy New Year!": Display "Happy New Year!" (no change)
"20136458cabd": Display "" (no string)
"4.52013672013892013": Display "4.5" (it's the first "2013" that matters)
"(212) 456-2013": Display "(212) 456-"
"2014 - 2013 = 1": Display "2014 - " (with trailing space)

If all that's right, it seems to me you need to use the FIND function to find "2013". Once you know what position it's in, you can use the LEFT function to keep just the parts of the character string that come before that position. Does that tell you what you need to know, or do you need a little coaching to use those two functions?

[Pecoflyer]

Perhaps =left(A1,len(A1)-4)

[BobBridges]

I was going to correct Pecoflyer's solution ("no, that just removes the last four bytes of string whether or not they're '2013' "), and checked your samples again. I see I got it wrong; it's not the first "2013" that matters, it's the last. And I thought you had at least one where more text appeared after "2013", but in all your examples every string ends that way.

So go over those sample strings again and tell me how you want them treated. That'll tell you whether Pecoflyer's solution will work for you. If it won't, your answers to the samples should enable either of us to define one that will.