#1
|
|||
|
|||
Excel Help: Making text C&P usable
I'm pasting some text (runlines) in from the web and I'd like to be able to manipulate the numbers. Most are fine but some come in the form of " +1.5 -153" and I can't figure out a way (macro?) to easily convert it to a usable format. All I'm interested in is the last figure, "-153" in the example.
If anyone could be of help it'd be appreciated. |
#2
|
|||
|
|||
Re: Excel Help: Making text C&P usable
If there is always a space between the last number you are interseted in and the rest of it you can play around with the text functions. For instance, if F15 is the cell you have put "+1.5 -153" you can get -153 using this:
=RIGHT(F15,SEARCH(" ",F15,1)) If there are leading spaces you can increase the last "1" in the search function to start looking later in the string. |
#3
|
|||
|
|||
Re: Excel Help: Making text C&P usable
Thanks for the help Kerowo, it's working like a charm.
|
#4
|
|||
|
|||
Re: Excel Help: Making text C&P usable
Probably wouldn't hurt to do a TRIM on F15 before you parse it. C/Ping from a browser can be pretty inconsistent. Sometimes you might get a space, sometimes you might get 2, or even none.
|
#5
|
|||
|
|||
Re: Excel Help: Making text C&P usable
If all you care about are numbers or text that are in a cell based format on the web site, copy the text then paste it into notepad, then copy THAT text and paste it into Excel. It will properly delinate the text then instead of smashing things into one cell that don't belong together in one cell.
All depends on the quality of the source though...for me its worked like a champ. |
#6
|
|||
|
|||
Re: Excel Help: Making text C&P usable
[ QUOTE ]
...copy the text then paste it into notepad, then copy THAT text and paste it into Excel... [/ QUOTE ] Or you can try Edit | Paste Special | Values (or right-click). |
|
|