Extract Specific Character Count From Field

[DataWhisperer]

Good day,

I have a data set with a field which has free form captured data, there are values which are only of 10 characters and I want to try pull each value that is 10 characters long into it's own field. Any ideas? Subfield doesn't seem to work as there are random delimiters between the values due to the free form capture. 

Item Description
Meter Numbers: 14339931983 and 14339932049
Meter Numbers: 07142285035, 07142285050, 07142285100, 07142285209, 07166767603, 07265238316 and 07265264668
Meter Number: 14363165755
14299841594
37242709626 AND 37242759852
14428988233
07148283174
37232477309

figure 1: Current date set

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7
14339931983	14339932049
07142285035	07142285050	07142285100	07142285100	07166767603	07265238316	07265264668
14363165755
14299841594
37242709626	37242759852
14428988233
07148283174
37232477309

figure 2: How I want my data to look

[MendyS]

@DataWhisperer 

There is a solution, it's a bit confusing, I'll try...

The first step - to download all the characters that are not numbers,

Second step - for each row will be a key that is the original value (without the letters dropped in step 1)

Third step - to split into two columns, first column will be the key and second column will be the splitting of the values using the space character,

Step four - to make a cross table.

Please let me know where you are getting stuck and if you need help, 
best

[Or · Accepted solution]

Something along the lines of:

Load Mid(Item,(10*(IterNo()-1)) + 1,10) as Item_New

While IterNo()*10 < Len(Item);

Load KeepChar(Item,'0123456789') as Item

From SomeTable;

Might not be precise but that should be the right neighborhood and you can tweak it as needed.

[barnabyd]

G'day @DataWhisperer, what an interesting puzzle!

I would start by cleaning the data to produce something consistent to work with. Based on the sample you have provided:

• trim off the leading and trailing spaces
• convert all text to lower case to reduce the number of combinations
• convert each type of delimiter to one that you prefer - this seems like an impossible task but you'll find that there aren't as many variations as you think - probably less than 10 replace() statements

Something like this:

replace( replace( lower( trim( [Item Description] ) )
                , 'and', ',' )
       , 'other delimiter', ',' )

After this, you should be able to use the subfield().

I hope this helps.

Cheers, Barnaby.

[MarcoWedel]

Hi, another solution might be:

mapNonNum2Space:
Mapping
LOAD Chr(RecNo()), ' '
AutoGenerate 65535
Where not IsNum(Chr(RecNo()));

mapPurgeMultiSpace:
Mapping
LOAD Repeat(' ',100-RecNo()), ' '
AutoGenerate 98;

table1:
Generic
LOAD *,
     'Field'&AutoNumber(RowNo(),RecNo()),
     SubField(Trim(MapSubString('mapPurgeMultiSpace',MapSubString('mapNonNum2Space',[Item Description]))),' ')
Inline [
Item Description
Meter Numbers: 14339931983 and 14339932049
Meter Numbers: 07142285035, 07142285050, 07142285100, 07142285209, 07166767603, 07265238316 and 07265264668
Meter Number: 14363165755
14299841594
 37242709626 AND 37242759852
14428988233
07148283174
37232477309
0123456789,9876543210
abc1234567890defg0987654321
numbers:2345678901/1098765432/123456789
there1234are567numbers89012hidden3456789in0123this4567text890
] (delimiter is ';');