Separating data from a cell

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

"Todd" <[email protected]> wrote in message
news:%[email protected]...
> 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!!!
>
>

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)?




"Bob Umlas" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Todd" <[email protected]> wrote in message
> news:%[email protected]...
>> 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!!!
>>
>>
>
>

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
> > E(A1,".",CHAR(21),4))+3-FIND(CHAR(17),SUBSTITUTE(A1,"$",CHAR(17),2)))

"Todd" <[email protected]> wrote in message
news:[email protected]...
> 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)?
>
>
>
>
> "Bob Umlas" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Todd" <[email protected]> wrote in message
> > news:%[email protected]...
> >> 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!!!
> >>
> >>
> >
> >
>
>

Thanks again!!!


"Bob Umlas" <[email protected]> wrote in message
news:[email protected]...
> 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
>> > E(A1,".",CHAR(21),4))+3-FIND(CHAR(17),SUBSTITUTE(A1,"$",CHAR(17),2)))
>
> "Todd" <[email protected]> wrote in message
> news:[email protected]...
>> 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)?
>>
>>
>>
>>
>> "Bob Umlas" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> > "Todd" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> 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!!!
>> >>
>> >>
>> >
>> >
>>
>>
>
>