how to remove ' from leading numbers

[psrs0810]

I am trying to remove the ' from leading values. I have used trim, clean, substitute, but non have worked. suggestions?

 

[prajul89]

=RIGHT(A1,LEN(A1))

try this

 

[Xander117]

Would this work? I have used it for removing the first character for alot of things. Its from someone on here.

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If Left(c, 1) = "'" Then c = Mid(c, 2)
    If Right(c, 1) = "'" Then c = Left(c, Len(c) - 1)
    c = Trim(c)
Next
End Sub

 

[psrs0810]

prajul89 - I was not able to get your formula to work.
The cells are formatted as general. and the values look something like '1100A1234
'1100A2345

every time I paste values, the ' is still there.

Xander117 - I would like to use the macro, but most people I work with do not know how to use them. So I am trying to avoid VBA, at this moment

 

[Rick Rothstein]

Select your column of apostrophed numbers (do each column separately if they are spread out in more than one column), bring up the "Text To Columns" dialog box** for your version of Excel and hit the Finish button as soon as the dialog box appears... your numbers should be normal numbers afterwards.

** XL2003 - Data/Text To Columns on the menu

** XL2007 - Data tab, Data Tools panel, Text To Columns button

 

[prajul89]

prajul89 - I was not able to get your formula to work.
The cells are formatted as general. and the values look something like '1100A1234
'1100A2345

every time I paste values, the ' is still there.

so you are saying the value of cell is '1100A2345 then the view in the cell you are getting is also '1100A2345.

if so then =RIGHT(A1,LEN(A1)-1) might help.

 

[taurean]

And what do you get when you try:
=ABS(A1)

 

[prajul89]

And what do you get when you try:
=ABS(A1)

will that work with alphanumeric values??

 

[taurean]

will that work with alphanumeric values??

No it won't. Thanks for pointing that out. This is, however, typically used for making Text-like format entry for numbers.

Isn't it strange that formula is inheriting the formatting? I tested with the following formula and it works correctly (4th row alignment, Text like on the left and Number on the right). Edit: Doesn't come out correctly with html maker!

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 125px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>1100A2345</TD><TD>1100A2345</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>1100A2345</TD><TD>1100A2345</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>1100A2345</TD><TD>1100A2345</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">100</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(ISERROR(ABS(A1)),A1,ABS(A1))</TD></TR><TR><TD>B2</TD><TD>=IF(ISERROR(ABS(A2)),A2,ABS(A2))</TD></TR><TR><TD>B3</TD><TD>=IF(ISERROR(ABS(A3)),A3,ABS(A3))</TD></TR><TR><TD>B4</TD><TD>=IF(ISERROR(ABS(A4)),A4,ABS(A4))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[psrs0810]

=RIGHT(A1,LEN(A1)-1) - did not work. It trimmed off the first value of everything.
=abs - did not work.

the only thing that worked was text to column and converted the needed columns to text in order to have my vlookup find the values.

thanks everyone. I will keep note of those formulas for other uses