eliminate leading quote in SAP download

  • Thread starter Thread starter BlackBayou
  • Start date Start date
B

BlackBayou

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
 
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
 
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.
 
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.
 
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
 
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 getrid
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







- Show quoted text -
Click to expand...

None of the above method will work because the single quote is not
charecter but is is used as prefix charecter to denote that the cell
contains a txt value.It is not part of cell contents.

Very simple method is there.Just copy the entire range in Notepad.All
single quote will be out.Then again copy paste it in previous range.

But if it is not single quote every other method will work.
 
Back
Top