Formula for pulling multiple values from one cell

[honorbum]

I have a dilemma. I have a cell containing multiple values separated by commas, for instance

12345,m349t,987v4

I need to pull these values out and list them separately. Any suggestions?

 

[kim00]

honor - Format | Text to Columns

Leave delimited check and hit next - under delimeters check "comma" then hit Next then Finish.

...kim

 

[kjv1611]

The method mentioned by kim00 should be "Data Menu" then "Text to Columns":

Split the contents of cells across multiple cells
You cannot split a cell or range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that was not previously merged. You can, however, divide the contents of unmerged cells and display them across other cells.

Select the cell, the range of cells, or the entire column that contains the text values that you want to divide across other cells. A range can be any number of rows tall, but no more than one column wide.
Important Unless there are one or more blank columns to the right of the selected column, the data to the right of the selected column will be overwritten.

On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.
Note For help with completing all the steps of the wizard, click Help in the Convert to Text Columns Wizard.

Or if you are willing to try VBA, you can use the Split function, and then create an array variable of the values, and then spit them out to cells in a given range.

Something like this:

Private Sub SplitItOut()
  Dim strValue() As String
  Dim r as Long 'Row
  Dim c as Long 'Column
  Dim x as Long 'For counting up rows columns in same row
  Dim a as Integer 'For counting up in the array

  r = ActiveCell.Row
  c = ActiveCell.Column
  strValue = Split(Range(r,c,), ",")
  x = c + 1
  a = 0
  For x = c + 1 To UBound(strValue) + 2
    Range(r,x).Value = strValue(a)
    a = a + 1
  Next x

End Sub

This code isn't totally tested, so it's possible you'll find you'll need to change the terminology around a bit - for instance, I don't remember whether it'd be best to use "Range" or "Cells" for this instance. But hopefully it'll give you a starting point.

If you do end up wanting to go the VBA route, try posting this question over at forum707.


The text to columns would be best for a single time. The VBA option would be something you could work out if it is going to need be done on a regular basis. Of course, maybe you can get the Text to Columns to work in VBA as well... if so, that'd be the easiest even from within VBA.

--

"If to err is human, then I must be some kind of human!" -Me

 

[honorbum]

Thanks for the info. The problem I'm now having with Text to Columns is that it's splitting the formula in the cell and not the returned data from the formula. Hmmmmm.

Here's the formula in Cell A:

=IF(N10="", AC10, N10)

Here's the returned data:
A
EA1313,EA1314

Here's the text to column return:
B C D
=IF(N10="" AC10 N10)

 

[AnotherHiggins]

You can copy the column containing formulas, then paste special > values. Then do the Text-To-Columns.

If you want to leave the formulas intact and bring the values over to other cells, then you could use variations of the LEFT, MID and RIGHT functions.

But I have a couple of questions. In your first example, there are three sections, in the second there are only 2. In the first example, there are always 5 characters in each section, in the second the first section has 6 characters.

You'll want a formula that always works. To help you come up with that, we'll need to know what all of the variations look like. Other than being separated by commas, are there any other similarities in the data you'll be chopping up?

Before going any further, will the Text-To-Columns work for you? If not, please post back with more info.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[honorbum]

The values in the second example are real data. The actual values will range from 4 to 7 digits. The number of values in the cell can range from 1 to many but normally any multiple values are 2 to 4. Unfortunately in that field, it is manually populated and comes with many variations as far as delimiters, however the comma is the most common. Text to columns works (the paste/special worked) and spreads the values into other columns however this may not be what I'm looking for. I need them all in one column for a pivot table.

 

[AnotherHiggins]

->I need them all in one column for a pivot table

So you want to turn [tt]
A
EA1313,EA1314[/tt]

into[tt]
A
EA1313
EA1314[/tt]

?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[honorbum]

Yes, but keep in mind, I have a list of cells in the column which are in the hundreds with data on both sides. I can always move the column to the end of the table so the right side is vacant.

 

[xlbo]

comes with many variations as far as delimiters

You are up a creek I'm afraid - if there is no logic that can determine what the seperator is or how many seperators there are, it will be a long hard manual slog

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[honorbum]

Well the good thing is that I don't have to have 'exact' data, I've been told to get what I can out of it because that's better than nothing.

Thanks for the help all!

 

[kjv1611]

If you can just get what you can, then maybe you should do it in a few courses. If you know the most common delimiters, you can maybe do a few different "text to columns" routines until you've gotten at least most of it extracted.

Just a thought, anyway, it seems to me that would work.

--

"If to err is human, then I must be some kind of human!" -Me