How to turn a scientific notation E number into a string of text?

[bigscientist]

Hi! It's my first time here. I need to develop a formula for the following example:

8100 <--This is what I input
8.10E+3 <-- an intermediate I'll need (pretty easy to convert to this format)
8103 <-- another intermediate (basically the . E and + need to go away)
81030 <-- if the E notation is "+" I need to add a 0 to the end of the string; if the E notation is "-" then I need to add a 1 to the string

In a nutshell, I'm trying to automatically generate 81030 from 8100. Does that make sense? Here's another example:

Input: 0.777
7.77E-1
7771
77711 <-- the number I need

The problem I'm having is that excel can't see the E notation as a character, as far as I know. Since it only sees it as a number I can't extract the notation and make it become a character. Any ideas? Thanks!!

[MrShorty]

In order for Excel to see the "E", it needs to be converted to text -- which you can do using the TEXT() function =TEXT(8100,"0.00E+0")
Then find the + (or -) and concatenate the appropriate value on the end =TEXT(...)&IF(ISERROR(FIND("+",TEXT(...)),1,0)
Then replace the "E+"/"E-" empty string =REPLACE(previous function,5,2,"")

Something like that should work.

[bigscientist]

Thanks so much MrShorty. Now I get how to turn my number into a scientific notation and text nonetheless. Thank you.

I'm confused about the 2nd part however. So with the 8.10E+3, my thought is I need to search for + or - and add 0 to the end of the string if it's + and 1 at the end of the string if it's a -.

What's the exact formula for that? (I tried what you typed, didn't work for me). Whatever this is, I now would have "8.10E+30"

After that I would remove the "." and the "E+" or "E-" and I can do =REPLACE(cell,2,1,"") to remove the ".", getting 810E+30.
And I can run a similar formula =REPLACE(cell,4,2,"") to remove the E+ or E-.

So it seems my only issue remaining is going from 8.10E+3 to 8.10E+30 or in the minus case, 8.10E-3 to 8.10E-31.

Thanks again!

[MrShorty]

It looks like it should have worked -- unless I got parentheses out of place? =TEXT(8100,"0.00E+0")&IF(ISERROR(FIND("+",TEXT(8100,"0.00E+0"))),1,0) That's what happened -- I missed a closing parenthesis on the FIND(...) function. I suspect Excel prompted you for a correction, but Excel gave you the wrong correction. That's the trouble with nesting too many functions together -- I always get a parenthesis out of place.

Do you understand what that step is trying to do? It is using the FIND() function to see if there is a + character. If there is, then the FIND() function will not return an error, so the IF() function will return the "value_if_false" argument (the 0). If there is a - character, then the FIND() function will return an error, and the IF() function will return the 1. Of course, the result of the IF() function is tacked on to the end of the text string.

[bigscientist]

I literally LOLd just now at how amazing you are. And I understand exactly what the step is doing now (thank you for the explanation in the 2nd paragraph). Thank you!!!!!

Ok last bonus question for today - any idea how to combine the =REPLACE steps I mentioned? I currently have it happening in 2 different cells, the "." then the "E +/-". If it's complicated, it's not a big deal at all. I plan to hide all these cells anyway.

EDIT: I'll keep it the way it is, haha. We should both get to enjoy the weekend. Thanks again

[MrShorty]

As long as you keep track of the order they are performed in (remove "." first or remove "E?" first), it should be fairly simple to nest the two REPLACE() functions inside of each other.
=REPLACE ( REPLACE ( "8.10E+30",2,1,"" ) ,4,2,"" ) or
=REPLACE ( REPLACE ( "8.10E+30",5,2,"" ) ,2,1,"" )

Assuming I got the parentheses right in this case.

(Spaces added because the site's firewall decided it didn't like them without the spaces)