Solved: Remove ^ From Begining of Every Cell

[sam314159]

I've tried searching for this on these forums and on Google but I'm not having luck with the ^ symbol.

I have a bunch cells with numbers in them but can't handle them as numbers because they are all formatted with the ^ symbol infront or a ' symbol.

I tried using 'Ctrl'+'F' to find ^ and replace with a space but no results are returned.

1. Why are these there?

2. Can I prevent them from being added to cells? (The sheet I am working with was sent to me by a vendor)

3. Is there anyway to mass remove them?

[mdmackillop]

In Edit/Replace, search for ~^
Th "~" is required as ^ is used in wild card searches.

[sam314159]

I tried search for ~^ and still no results returned even though ever cell looks something like thsi:

^70.356.

Note that the ^ only appears in the Excel 'Formula Bar' and not in the cell itself, I probably should have mentioned that earlier.

[mdmackillop]

Can you post a sample worksheet?

[sam314159]

Sure, thanks for looking into it MD.

[mdmackillop]

I'm not seeing ^ in your attachment
Try =CODE(MID($B1,COLUMN()-1,1)) in B26. Copy across and down to get the code of all characters in Column B.

[sam314159]

This is how the sheet appears to me.

[SamT]

I don't see it in the formula bar, but I get the green triangle warning that they are all formatted as text or are preceded by an apostrophe.

Get it in every column with numbers too.

Format cells as number doesn't work.

Clear contents, format cell as number, type new number doesn't work.

Only thing that works is to select the cell warning popup and choose "Convert to number". This works for any contiguous range of cells.

Exactly how are these values put into the cells?

[Jan Karel Pieterse]

Select cells, Data, text to Columns, finish.

[sam314159]

Select cells, Data, text to Columns, finish.

That worked perfectly, thanks Jan.

I wrote some code in a macro to do this for every column in every sheet in an open workbook and it works fine. I am posting it below incase someone else has a need for it in the future.

I hope the code below is not too primitive. I'm really an eletrical engineer posing as a programmer to hang out with the cool poeple!


Option Explicit
'STA 03/25/2010
'The following subroutine loops through every worksheet in the open workbook and reformats
'all cells that contain a number from 'text' to 'number'
Sub FormatAllTextToNumbers()

'Variable Declaration
Dim currentWorksheet As Worksheet 'Used as a temporary worksheet object
Dim LastCol As Integer 'Used as upper limit of column loop
Dim c As Integer

'This loops through every worksheet in open workbook
For Each currentWorksheet In ActiveWorkbook.Worksheets

currentWorksheet.Select

'Find upper limit of column loop
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

'Loop through every column in current worksheet
For c = 1 To LastCol

'Select one row at a time
Columns(convertToColumnLetter(c) + ":" + convertToColumnLetter(c)).Select

'The following block of code converts a column to number
Selection.TextToColumns Destination:=Range(convertToColumnLetter(c) + CStr(1)), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

'Error Handler
On Error Resume Next 'Will continue if an error results

Next c
Next currentWorksheet

Worksheets(1).Select

End Sub


'STA 03/25/2010
'The following function returns a column letter for each number
Public Function convertToColumnLetter(columnNumber As Integer) As String
If columnNumber > 26 Then
convertToColumnLetter = Chr(Int((columnNumber - 1) / 26) + 64) & Chr(((columnNumber - 1) Mod 26) + 65)
Else
convertToColumnLetter = Chr(columnNumber + 64)
End If
End Function

[SamT]

Jan,

Thanks for sharing. I love engineers code.

You can avoid selecting and activating each sheet.

With currentWorksheet

'Find upper limit of column loop
LastCol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
yaddah
some loops, whatever
yaddah
End With


Can you share your code for convertToColumnLetter? I have something similar and would like to compare it to yours.

[sam314159]

Sam, the code for convertToColumnLetter is in that same VBA code block above.

[SamT]

It were hiding fum me at the bahtum.

Thanks.

Seven lines vs my 70 lines. Mucho thanks.

Just looking at it, it looks like yours' is limited at column #256. I wrote mine to handle column #999,999,999, but even extending yours, it will still be smaller and more elegant than mine.