Separating data from a cell

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I have data that is copied and pasted into Excel from another software
program. The problem is that it will only copy over as unfomatted text so I
get one long string of data in just one column. There are 2 pieces of data
that I need to pull from this string.

The first is a piece of cake because it is just the first 5 characters of
the cell so I just made a formula in B1 that says "= left (A1 , 5)"

The second one is what I'm having trouble with. The first character of the
data I need to pull is always the 2nd dollar ($) sign in the string. The
end is always 2 characters after the 4th decimal (.) in the string. Does
anyone know how to put this into a formula or create some VBA that will
accomplish this? Thanks!!!
 
If the cell in questino is A1:
=MID(A1,FIND(CHAR(17),SUBSTITUTE(A1,"$",CHAR(17),2)),FIND(CHAR(21),SUBSTITUT
E(A1,".",CHAR(21),4))+3-FIND(CHAR(17),SUBSTITUTE(A1,"$",CHAR(17),2)))

Bob Umlas
Excel MVP
 
Wow! You're awesome! I just have one small problem. Excel is not treating
the data as numbers... I can't "Sum" the column. Is there a way to make the
data calculateable (if that is a word)?
 
Multiply by 1. That is, 1*the formulas I gave you. Like
=1*MID(A1,FIND(CHAR(17),SUBSTITUTE(A1,"$",CHAR(17),2)),FIND(CHAR(21),SUBSTIT
UT
 
Thanks again!!!


Bob Umlas said:
Multiply by 1. That is, 1*the formulas I gave you. Like
=1*MID(A1,FIND(CHAR(17),SUBSTITUTE(A1,"$",CHAR(17),2)),FIND(CHAR(21),SUBSTIT
UT
Click to expand...
 
Back
Top