Remove Bitcoin Symbol From Number

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows
I paste my current cryptocurrency exchange order into column A. I use several macros on it then clear the contents for the next trade. So the contents of Column A changes with every trade. The problem I am having is now Bittrex puts the Bitcoin symbol at the beginning of the number like the following ₿0.00051082

I need a macro that will just "get rid of the Bitcoin symbol" leaving the rest of the number formatted as a number.

I have tried a macro with find and replace but it does not know what the symbol is.

I have tried a macro with deleting the bitcoin symbol but it leaves the rest of the number there in the macro and I can not reuse the macro on new orders because it leaves the old numbers there. I Just need to get rid of the only the bitcoin symbol each time I run the macro.

I just cannot figure it out. Please help.

Thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,579
Office Version
  1. 365
Platform
  1. Windows
use text to columns to split the column of data by the number of characters. Then you can delete the column with the Bit Coin character.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,220
Office Version
  1. 365
Platform
  1. Windows
Then you can delete the column with the Bit Coin character.
Just don't import that column in the first place then no need to delete anything.

Try this (adjust column to suit)

VBA Code:
Sub RemoveBitcoin()
  Columns("E").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
End Sub


Or if you have a header that you want left alone ..
VBA Code:
Sub RemoveBitcoin()
  Range("E2", Range("E" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
End Sub
 
Last edited:

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,826
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Bitcoin figures are in Column A starting at A2.
Result in Column B
Code:
Sub Maybe_A()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Offset(, 1) = Mid(c, 2, 99)
    Next c
End Sub
Bitcoin figures are in Column A and are replaced without first character.
Code:
Sub Maybe_B()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Value = Mid(c, 2, 99)
    Next c
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,220
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another, probably better, option to try (which I think is what you were trying to do) is

VBA Code:
Sub RemoveBitcoin_v2()
  Columns("A").Replace What:=ChrW(8383), Replacement:="", LookAt:=xlPart
End Sub
 

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows
Thank you very much for your time and brain power. I used your last one and it works with no problems.
It was pointed out to me that in the future other crap may enter the data that I might have to keep and she advised me to ask if you could to limit the removal of the bitcoin symbol just to column A and rows 7 through row 26.

Thanks Again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,220
Office Version
  1. 365
Platform
  1. Windows
Sure
VBA Code:
Sub RemoveBitcoin_v3()
  Range("A7:A26").Replace What:=ChrW(8383), Replacement:="", LookAt:=xlPart
End Sub
 

Forum statistics

Threads
1,144,627
Messages
5,725,371
Members
422,621
Latest member
dfrare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top