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

[FishSea]

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.

 

[Rick Rothstein]

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)...

[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]

Thanks, that's perfect. I was thinking of this in a much too complicated fashion, that helps a lot!