How do I remove a comma from a value

Oldie

New Member
Joined
Jul 31, 2007
Messages
9
I don't think I've got this HTML maker thing right yet.
Following on from the help I received yesterday,I would now like to extract the value to the right of the cells containing Cd.
I can get 57.0, but I don't know how to get rid of the comma following the value. You will see that the values aren't always four characters in length. ie. 54 - 55 - 52.5.
Again, the values aren't always in the same column.
Any suggestions will be much appreciated.

F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

E1 =


A B C D E
1 5, Cd 57.0, (53.5), 57.0,
2 8, Cd 54 -1.5,
3 Cd 55 -3.0, (54.5),
4 6, Cd 52.5 -0.5,
Sheet1
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
an UDF, if you want to try.
1) hit Alt + F11 to open vb editor
2) go to [Insert] - [Module] then paste the code onto the right pane
3) close the Window by clicking "x"
Use in cell like
=Oldie(A1:D1, "Cd", True) ' True for Case sensitive
Code:
Function Oldie(rng As Range, txt As STring, CompreMode As Boolean) As Variant
Dim myTxt As String
myTxt = WorksheetFunction.Trim(Join(Application.Transpose(rng.Value)))
With CreateObject("VBScript.RegExp")
     .Pattern = txt & "\s?([^\s,]+)"
     .IgnoreCase = Not CompreMode
     Oldie = .replace(myTxt, "$1")
End With
End Function
 
Upvote 0
So, you can get the value you want, but sometimes it has a comma and sometimes it doesn't. You want a way to get rid of the comma. Try using the following function:
Code:
=SUBSTITUTE(B1,",","")
This will find all commas in the string in cell B1 and strip (remove) them by changing them to an empty string.
 
Upvote 0
Thanks bobinnc, that worked a treat.
And thanks to you to jindon, I will try your suggestion.
Being a new boy on the block, I haven't come to grips with displaying the worksheet yet.
I followed the suggestions and installed the HTML maker but can't get it to work yet.
Thanks again to all.
 
Upvote 0
When you first get into the Excel board look at Von Pookie suggestions for installing HTML
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top