Remove certain numbers (too large ones) from a single cell containing comma separated values

FishSea

New Member
Joined
Jun 9, 2016
Messages
11
My basic need is to remove numbers over a certain amount (or over a certain number of characters, essentially the same for my purposes) from a cell that contains a large (unknown) amount of numbers separated by commas.

For example, if I had the following cell, how would I remove any values over say 99999999 (8 chars long)?

0,0,0,0,02517485804,0,23412093445328953,10017455,10017455,0,10073181,10073181,0,0,10294388,0,0,10300434,0

<tbody>
</tbody>

A couple of notes - it's not exactly an 'unknown' amount of numbers - while the amount is ever changing, I can get the amount of numbers separated by columns in the particular cell with the following:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

Additionally, there are a lot of zeroes in each cell, a solution that removes them is perfectly fine but not entirely needed - they aren't an issue for my purposes. For full context, I'm running a MySQL query using the values in the cell, and basically just need to avoid going above BigInt values, but would like to do so in an automated fashion. At the same time, the amount of values is never going to be big enough to slow down the query by any noticeable length of time, so leaving the zeroes in isn't really an issue. (I've been told not going above BigInt is what I should aim for, as going above that is slowing the query down, but realistically I could limit it to Int size for my purposes).

The simplest one-off solution would probably be to use Text to Columns, transpose the row to a column, then filter and remove everything above a certain amount, but I'm looking for a more automated solution (possibly could go with an automated version of that, but I'd prefer to use fewer steps if possible).

An Excel formula would probably preferred, that being said if VBA is used I'd prefer to specify the cell (not use ActiveCell), and have it output the result in a different cell, to help with testing/ensuring no issues arise.

That being said, if there's a simpler way to make a mysql query avoid actually checking with values that are too large, that would be workable as well, though somewhat off-topic from this forum.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,347
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I'll be surprised if you can do this with a single formula. Here is a macro that should work (output to the next cell to the right)...
Code:
[table="width: 500"]
[tr]
	[td]Sub RemoveLargeNumbers()
  Dim X As Long, Cell As Range, Parts() As String
  Set Cell = Application.InputBox("Select the cell to process...", Type:=8)
  Parts = Split(Cell.Value, ",")
  For X = 0 To UBound(Parts)
    If Len(Parts(X)) > 8 Then Parts(X) = ""
  Next
  Cell.Offset(, 1).Value = Replace(Application.Trim(Join(Parts)), " ", ",")
End Sub[/td]
[/tr]
[/table]
 

FishSea

New Member
Joined
Jun 9, 2016
Messages
11
Thanks, that's perfect. I was thinking of this in a much too complicated fashion, that helps a lot!
 

Forum statistics

Threads
1,141,315
Messages
5,705,699
Members
421,406
Latest member
kluna90

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