Prev: Referencing conditional format is another workbookHI
Next: Arrays of Controls
From: BlackBayou on 4 Jun 2010 16:31
Downloaded list looks like this:

'Z1753
'Z2857
'Z11288 etc

I need to remove the quotes in order to lookup values from a list that does
not have the quotes. Simple next>next>finish does not work, neither does
find>' or find>space
From: Michelle on 4 Jun 2010 16:56
Here's two ways that work...
1. Include this function in a module in the workbook with the data (or your
personal macro worlkbook)
then you can use it like any worksheet function, it will make a duplicate of
you data without quotes.
'=================================
Function LoseQuotes(InputString As String) As Variant
LoseQuotes = Replace(InputString, "'", "")
End Function
'=================================

Or you can put this in a module and run it on an area by selecting the range
and running the macro
'=================================
Sub EraseQuotes()
Dim xCell As Range
For Each xCell In Selection
xCell.Formula = Replace(xCell.Formula, "'", "")
Next xCell
End Sub
'=================================
hth

M


"BlackBayou" <BlackBayou(a)discussions.microsoft.com> wrote in message
news:27F84A45-F6E4-4BE1-85F1-9C2DB7130343(a)microsoft.com...
> Downloaded list looks like this:
>
> 'Z1753
> 'Z2857
> 'Z11288 etc
>
> I need to remove the quotes in order to lookup values from a list that
> does
> not have the quotes. Simple next>next>finish does not work, neither does
> find>' or find>space

From: OssieMac on 4 Jun 2010 17:05
Don't know what you mean by "Simple next>next>finish does not work". However,
can you see the quote in the Excel cells or is it simply there in the formula
bar indicating that it is text and not numeric?

If only in the formula bar, assuming data in column A, then insert a column.
Ensure the column is formatted General and has not inherited a Text format.
Enter the formula =A1 and copy down. Select the new column and Copy >
PasteSpecial > Values over top of itself. Delete the original column.

If you can see the quote in the cells then in another blank cell enter the
formula
=CODE(LEFT(A1,1)) to ensure that the quote is in fact code 39. If it is code
39 then you should be able to use Find/Replace to remove the character. If
not code 39 then use Find/Replace and enter the character code to find by
holding the Alt key and enter the the code as a 4 digit number with leading
zeros. Leave the Replace field blank.


--
Regards,

OssieMac


"BlackBayou" wrote:

> Downloaded list looks like this:
>
> 'Z1753
> 'Z2857
> 'Z11288 etc
>
> I need to remove the quotes in order to lookup values from a list that does
> not have the quotes. Simple next>next>finish does not work, neither does
> find>' or find>space
From: OssieMac on 4 Jun 2010 17:40
I forgot to say that the following must be done using the numeric keypad. You
cannot use the numeric keys above the alpha characters.

If not code 39 then use Find/Replace and enter the character code to find by
holding the Alt key and enter the the code as a 4 digit number with leading
zeros.

--
Regards,

OssieMac


"OssieMac" wrote:

> Don't know what you mean by "Simple next>next>finish does not work". However,
> can you see the quote in the Excel cells or is it simply there in the formula
> bar indicating that it is text and not numeric?
>
> If only in the formula bar, assuming data in column A, then insert a column.
> Ensure the column is formatted General and has not inherited a Text format.
> Enter the formula =A1 and copy down. Select the new column and Copy >
> PasteSpecial > Values over top of itself. Delete the original column.
>
> If you can see the quote in the cells then in another blank cell enter the
> formula
> =CODE(LEFT(A1,1)) to ensure that the quote is in fact code 39. If it is code
> 39 then you should be able to use Find/Replace to remove the character. If
> not code 39 then use Find/Replace and enter the character code to find by
> holding the Alt key and enter the the code as a 4 digit number with leading
> zeros. Leave the Replace field blank.
>
>
> --
> Regards,
>
> OssieMac
>
>
> "BlackBayou" wrote:
>
> > Downloaded list looks like this:
> >
> > 'Z1753
> > 'Z2857
> > 'Z11288 etc
> >
> > I need to remove the quotes in order to lookup values from a list that does
> > not have the quotes. Simple next>next>finish does not work, neither does
> > find>' or find>space
From: ker_01 on 4 Jun 2010 18:59
To add to the other replies;

If your data /includes/ single quotes, in addition to the first one in the
cell, then you probably don't want to a full search/replace, but just get rid
of the first quote.

Consider creating an additional column (general format), and use a formula
like:
Raw data in A1, this formula in B1 as an example
=right(A1, len(A1)-1)

That should give you everything except that first character. Then
copy/pastespecial/values, and then delete the original column, leaving you
with only the adjusted data in your new column.

HTH,
Keith

"BlackBayou" wrote:

> Downloaded list looks like this:
>
> 'Z1753
> 'Z2857
> 'Z11288 etc
>
> I need to remove the quotes in order to lookup values from a list that does
> not have the quotes. Simple next>next>finish does not work, neither does
> find>' or find>space
 |  Next  |  Last
Pages: 1 2
Prev: Referencing conditional format is another workbookHI
Next: Arrays of Controls