VBA Insert Rows Based on Cells with comma

[Stephen_IV]

I have data that has values seperated by a comma in Range("D:D"). I need a single row for that data. Please see below! Thanks in advance!

Book5 A B C D 1 ID Name Sch Cert 2 111 Mr X School A 100 3 111 Mr X School A 120, 141, 156 4 222 Mr Y School Z 98,111 5 333 Mr A School D 980 6 444 Mr T School X 111,115,116,118,110 7 555 Mr C School C 98 Sheet1

I need the data to look like this:

Book5 A B C D 1 ID Name Sch Cert 2 111 Mr X School A 100 3 111 Mr X School A 120 4 111 Mr X School A 141 5 111 Mr X School A 156 6 222 Mr Y School Z 98 7 222 Mr Y School Z 111 8 333 Mr A School D 980 9 444 Mr T School X 111 10 444 Mr T School X 115 11 444 Mr T School X 116 12 444 Mr T School X 118 13 444 Mr T School X 110 14 555 Mr C School C 98 Sheet2

 

[Joe4]

On row three, you have spaces in between each number, so they are treated as 3 different values.
However, on rows 4 and 6, you do NOT, so it seems to consider it as one long number (if your values are entered as numbers and not text).

So, you need to either have a space between each value, or have the values enteres as text.
Otherwise, Excel is going to see 98,111 as one number, specifically "ninety-eight thousand one-hundred and eleven" and not "ninety-eight and eleven".

Can you confirm which is it, so we know how to create this code?

 

[Stephen_IV]

On row three, you have spaces in between each number, so they are treated as 3 different values.
However, on rows 4 and 6, you do NOT, so it seems to consider it as one long number (if your values are entered as numbers and not text).

So, you need to either have a space between each value, or have the values enteres as text.
Otherwise, Excel is going to see 98,111 as one number, specifically "ninety-eight thousand one-hundred and eleven" and not "ninety-eight and eleven".

Can you confirm which is it, so we know how to create this code?

Thanks Joe, it is one long number. Sorry about that.

 

[Joe4]

If you look at the formula box for cell D4, it then shows 98111, right? with no comma (as the formula box shows the unformatted values)?
That would make this a lot trickier, as the comma isn't really part of the value.

So, are you then saying that you have a mixture of entries, some with spaces like D3:
120, 141, 156
and some long numbers with no commas, i.e.
98111

It can be very difficult to work with inconsistent data, where you have a mixture of text and numeric entries which do not seem to follow any rules.
If you could standardize them, one way or the other (all numeric entries or all text entries), it would go a long way.

If dealing with long numbers, then is really want you want is the number, split in lengths of 3, going from the right to the left?

 

[Stephen_IV]

Thanks for the response. Yes, you are correct. the format is 100,101,102 no spaces. This is a State file and we don't have any control over the export.
In the case that we have 98,100. It should be 098,100 or 1,9 it should be 001,009 so technically 3 digits.

 

[Joe4]

OK, I am very confused now regarding the how the data actually appears in the file.
Note how the cell is formatted, and what is actually contained in the cells are two different things.
Formatting just changes how it appears on the worksheet, but not the actual value in the cell.
You can see how it actually is stored in the cell by selecting the cell, and viewing what it in the formula box.

How is this file coming over to you? As an Excel file, or some other type of file that you are importing into Excel?
Would it be possible to upload the file to some file sharing site for us to inspect exactly what the data looks like?
If it contains other sensitive data, you can really delete everything other than this column. I think that is all we really need to see.

 

[Stephen_IV]

This is an actual sample:

Book4
	A	B	C	D
1	EID	Name	School	EndorsementCodes
2	1111			92
3	1111			23,101
4	2222			71
5	3333			305
6	4444			15
7	5555			42
8	6666			15
9	7777			15
10	8888			92
11	9999			165
12	1234			15
13	1235			13,165
14	1236			1,068,107
15	1237			70
16	1238			13,102
17	1239			265
18	1240			5
19	1241			13,102
Sheet2

 

[Joe4]

That does not help me at all, as that just shows the formatted values in column D.
I need to see the underlying values, which is why I wanted the actual file.
If you are unable to provide that, they answer ALL the following questions.

If you click on cell D3, what does it show you in the formula box (f(x)) at the top?
What about cell D14? What does that show in the formula box?

If you enter the following formulas in any blank cells, what do they return:
=ISNUMBER(C3)
=ISNUMBER(D14)

 

[Stephen_IV]

TestFile.xlsm - Onehub

 

[Joe4]

OK, I opened it up, and it is definitely one single numeric entry in each cell (i.e. 23,101 is really just 23101).

You did not answer my question in how this file is coming over to you.
Is it coming over as an Excel file, or is it some sort of text file that you are importing into Excel (if it is, we can handle part of this issue in the import scripts)?

If it is coming over as an Excel file, where all those are numbers, then it sounds like you want to split the numbers, from right to left, in increments of 3.