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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

bobinnc

Active Member
Joined
Nov 4, 2006
Messages
281
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.
 

Oldie

New Member
Joined
Jul 31, 2007
Messages
9
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.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
When you first get into the Excel board look at Von Pookie suggestions for installing HTML
 

Forum statistics

Threads
1,181,606
Messages
5,930,854
Members
436,764
Latest member
avalladarez

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