Text Modification

[brooksc57]

I have coded this in an Excel VBA macro, but am looking for a way to do it within the script editor in qv.

as a reference these are part numbers.  The reason for even doing this is the lookup "clean" parts because end users slap the dashes and slashes in the wrong place sometimes...of course this whole thing leads to other issues, but their are checks in place for that.

anyway trying to solve this part number modification.

XY-123 should become  XY123   --- because letters and numbers can touch and the dash can be "removed" or not kept.

and

X1-23Y should remain  X1-23Y  ---- because two numbers cannot touch, if they already had a dash between them

and

X-Y123 should remain X-Y123  ----  because two letters cannot touch, if they already had a dash between them

in excel vba I grab the length of a field and read 3 characters at a time and make changes...in this case keeping or not keeping the dash based on istext or isum of the 1st and 3rd characters.  in excel vba I do this for a field row by row.

as you'd guess there are lot more rules than the one above, but any help with this one would get me close to solving the others.

thx

brooks

[sunny_talwar]

See if this post offers any help:

Finding text patterns in Data Columns

[johnw]

You can do it by generating a substring map.

Undash:
MAPPING LOAD
L & '-' & N as From
,L & N as To
;
LOAD
chr(ord('A')+recno()-1) as L
,text(iterno()-1) as N
AUTOGENERATE 26
WHILE iterno() <= 10
;

Main:
LOAD
Something
,mapsubstring('Undash',Something) as SomethingFixed
INLINE [
Something
XY-123
X1-23Y
X-Y123
];

[swuehl · Accepted solution]

What about

X1-Y123

?

Should this become X1Y123, numbers and letters can touch?

Maybe we need to expand the mapping table to contain also the combinations with L & N exchanged:

Undash:

MAPPING

LOAD

If(FirstRun, L & '-' & N , N & '-' & L) as From

,If(FirstRun, L & N, N & L) as To

;

LOAD

Div(Iterno(),10) as FirstRun,

chr(ord('A')+recno()-1) as L

,text(Mod(iterno()-1,10)) as N

AUTOGENERATE 26

WHILE iterno() <= 20

;

Something	SomethingFixed
X-Y123	X-Y123
X1-23Y	X1-23Y
X1-Y123	X1Y123
XY-123	XY123

[johnw]

Yep. Whatever the requirement is, we'd be trying to add rows that describe that requirement to our mapping table.