[SOLVED] change text in cell

Hello,

I have a worksheet with a column containing data in the format "###########"
I would like to change the values so that I can insert "dashes" at specific
locations, such as "##-####-####-#" for all the values in the column.

I tried the simple way...formating the cells, but when I run a formula, the
values don't match and I get an error, unless the dashes are actually
inserted.

Thank you in advance

Nelson wrote:
> Hello,
>
> I have a worksheet with a column containing data in the format "###########"
> I would like to change the values so that I can insert "dashes" at specific
> locations, such as "##-####-####-#" for all the values in the column.
>
> I tried the simple way...formating the cells, but when I run a formula, the
> values don't match and I get an error, unless the dashes are actually
> inserted.
>
> Thank you in advance

what does the formula do?
After formatting is the cell text? May post your format code.

Did you use the custom format?

Such as: ##-####-####-#

I tried this by entering 2345234553 in cells A1:A10 in then summing the total in A12 formatted C12 with the custom format above then enter the formula =A12 in cell C12 and it worked just fine. Give it a try and let me know if it works.

Ed

Only values stored as numbers or cells containing formulas that return
numbers can be formatted to change the appearance as you show.

What do you mean by run a formula?

If you actually mean run code, then you want to look at the Text property
rather than the value property of the cell to see the formatted appearance of
the cell. This assumes the formatting in the cell does work.

--
Regards,
Tom Ogilvy


"Nelson" wrote:

> Hello,
>
> I have a worksheet with a column containing data in the format "###########"
> I would like to change the values so that I can insert "dashes" at specific
> locations, such as "##-####-####-#" for all the values in the column.
>
> I tried the simple way...formating the cells, but when I run a formula, the
> values don't match and I get an error, unless the dashes are actually
> inserted.
>
> Thank you in advance

Thanks for the reply,

Sorry for not being explicit on my previous post. What I have is two
documents containing information about the same it. One worksheet has the
product number (formated as text) in the ########### format, while the second
worksheet has the product number in the ##-####-####-# format.

There is additional information in one worksheet that I need to extract via
a vlookup formula, but since they product numbers don't match (due to the
absence of the dashes), I get errors in the returned value.

So, I simply need to add the dashes to the values in the cells.

Hope this clarifies the problem.

"Tom Ogilvy" wrote:

> Only values stored as numbers or cells containing formulas that return
> numbers can be formatted to change the appearance as you show.
>
> What do you mean by run a formula?
>
> If you actually mean run code, then you want to look at the Text property
> rather than the value property of the cell to see the formatted appearance of
> the cell. This assumes the formatting in the cell does work.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nelson" wrote:
>
> > Hello,
> >
> > I have a worksheet with a column containing data in the format "###########"
> > I would like to change the values so that I can insert "dashes" at specific
> > locations, such as "##-####-####-#" for all the values in the column.
> >
> > I tried the simple way...formating the cells, but when I run a formula, the
> > values don't match and I get an error, unless the dashes are actually
> > inserted.
> >
> > Thank you in advance

Assume A1 on the sheet with the formula contains a literal string that looks
like ##-####-####-# and you are looking on sheet2 that in column A has
string values that look like ###########

then you could use a formula like

=vlookup(substitute(a1,"-",""),Sheet2!A:B,2,false)

--
Regards,
Tom Ogilvy


"Nelson" wrote:

> Thanks for the reply,
>
> Sorry for not being explicit on my previous post. What I have is two
> documents containing information about the same it. One worksheet has the
> product number (formated as text) in the ########### format, while the second
> worksheet has the product number in the ##-####-####-# format.
>
> There is additional information in one worksheet that I need to extract via
> a vlookup formula, but since they product numbers don't match (due to the
> absence of the dashes), I get errors in the returned value.
>
> So, I simply need to add the dashes to the values in the cells.
>
> Hope this clarifies the problem.
>
> "Tom Ogilvy" wrote:
>
> > Only values stored as numbers or cells containing formulas that return
> > numbers can be formatted to change the appearance as you show.
> >
> > What do you mean by run a formula?
> >
> > If you actually mean run code, then you want to look at the Text property
> > rather than the value property of the cell to see the formatted appearance of
> > the cell. This assumes the formatting in the cell does work.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Nelson" wrote:
> >
> > > Hello,
> > >
> > > I have a worksheet with a column containing data in the format "###########"
> > > I would like to change the values so that I can insert "dashes" at specific
> > > locations, such as "##-####-####-#" for all the values in the column.
> > >
> > > I tried the simple way...formating the cells, but when I run a formula, the
> > > values don't match and I get an error, unless the dashes are actually
> > > inserted.
> > >
> > > Thank you in advance

Nelson wrote:
> Thanks for the reply,
>
> Sorry for not being explicit on my previous post. What I have is two
> documents containing information about the same it. One worksheet has the
> product number (formated as text) in the ########### format, while the second
> worksheet has the product number in the ##-####-####-# format.
>
> There is additional information in one worksheet that I need to extract via
> a vlookup formula, but since they product numbers don't match (due to the
> absence of the dashes), I get errors in the returned value.
>
> So, I simply need to add the dashes to the values in the cells.
>
> Hope this clarifies the problem.

Make sure both of your lists are the same datatype - either string or
number. also, you need to alphabetize your lookup range when using
vlookup. hope this helps

AR

No, you don't have to alphabatize your range if your are looking for an exact
match which appears to be the case here.

--
Regards,
Tom Ogilvy


"ducky" wrote:

>
> Nelson wrote:
> > Thanks for the reply,
> >
> > Sorry for not being explicit on my previous post. What I have is two
> > documents containing information about the same it. One worksheet has the
> > product number (formated as text) in the ########### format, while the second
> > worksheet has the product number in the ##-####-####-# format.
> >
> > There is additional information in one worksheet that I need to extract via
> > a vlookup formula, but since they product numbers don't match (due to the
> > absence of the dashes), I get errors in the returned value.
> >
> > So, I simply need to add the dashes to the values in the cells.
> >
> > Hope this clarifies the problem.
>
> Make sure both of your lists are the same datatype - either string or
> number. also, you need to alphabetize your lookup range when using
> vlookup. hope this helps
>
> AR
>
>

Thanks for the reply.

What I have is actually in reverse. I have A1 with ########... and I need
to look up values that look like ##-###...

"Tom Ogilvy" wrote:

> Assume A1 on the sheet with the formula contains a literal string that looks
> like ##-####-####-# and you are looking on sheet2 that in column A has
> string values that look like ###########
>
> then you could use a formula like
>
> =vlookup(substitute(a1,"-",""),Sheet2!A:B,2,false)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nelson" wrote:
>
> > Thanks for the reply,
> >
> > Sorry for not being explicit on my previous post. What I have is two
> > documents containing information about the same it. One worksheet has the
> > product number (formated as text) in the ########### format, while the second
> > worksheet has the product number in the ##-####-####-# format.
> >
> > There is additional information in one worksheet that I need to extract via
> > a vlookup formula, but since they product numbers don't match (due to the
> > absence of the dashes), I get errors in the returned value.
> >
> > So, I simply need to add the dashes to the values in the cells.
> >
> > Hope this clarifies the problem.
> >
> > "Tom Ogilvy" wrote:
> >
> > > Only values stored as numbers or cells containing formulas that return
> > > numbers can be formatted to change the appearance as you show.
> > >
> > > What do you mean by run a formula?
> > >
> > > If you actually mean run code, then you want to look at the Text property
> > > rather than the value property of the cell to see the formatted appearance of
> > > the cell. This assumes the formatting in the cell does work.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Nelson" wrote:
> > >
> > > > Hello,
> > > >
> > > > I have a worksheet with a column containing data in the format "###########"
> > > > I would like to change the values so that I can insert "dashes" at specific
> > > > locations, such as "##-####-####-#" for all the values in the column.
> > > >
> > > > I tried the simple way...formating the cells, but when I run a formula, the
> > > > values don't match and I get an error, unless the dashes are actually
> > > > inserted.
> > > >
> > > > Thank you in advance