Forum Discussion
Replace parts of text that meets certain criteria in a text file
Hi Hans_Olsson
Thanks for the pictures. According to the few examples you posted earlier #OBJEKT strings appear to be formatted as below but with copy/paste on the Web we're never sure so please check this first with your actual data
The attached update highly/only relies on those consistent patterns (seems very consistent on your side according to your pictures :))
#OBJEKT<2 spaces>6<2 spaces>"2003"<1 space>"Kastrup 11.1"
#OBJEKT<2 spaces>25<1 space>"001"<1 space>"ŽTA 001"
Lorenzo Thank you very very much. The attached file clearly does the job. There's just one minor glitch. It requires #TRANS to start in position 1. In the actual file the #TRANS-lines begins with 3 blanks and #TRANS starting in pos 4. If I remove the 3 blanks the query works like a dream.
This is the first time I'm in contact with Power Query. I don't manage to bring up the actual query, to see what the code looks like and add the three 3 blanks or even better, make the query look for #TRANS independent of where it starts.
If it's an easy change it would be very much appreciated if you could make it and attach the updated file.
Regardless, big thanks for this!
Doable no problem. Question though: do you want to keep the <spaces> before #TRANS in the Out table? Or do you prefer to remove those leading <spaces> in ALL records, or only on the #TRANS record?
- If possible the preferred solution is to keep the <spaces> before #TRANS in the Out table so beginning <spaces> in the In and Out tables are identical.
Going forward the current files (In table) will be made obsolete and the Out tables will be the new "current" files.Hans_Olsson This is embarrassing but the #TRANS-records are not the only type that needs to be modified. In addition the #OBJEKT 6 and #OBJEKT 25 records needs modification, too.
These are the declarations for the #TRANS "6" and "25" records already processed.
Current content Modify to #OBJEKT 6 "2003" "Kastrup 11.1" #OBJEKT 6 "00002003" "Kastrup 11.1" #OBJEKT 6 "1001-04" "Welcome Hotel" #OBJEKT 6 "00100104" "Welcome Hotel" #OBJEKT 25 "001" "ŽTA 001" #OBJEKT 25 "0001" "ŽTA 0001" #OBJEKT 25 "220" "ŽTA 220" #OBJEKT 25 "0220" "ŽTA 0220" This is the last addition. The case won't continue to grow after this.
I'm truly sorry I didn't provide the full picture from start.
Quite embarrassing :))
Let's make sure we're on the same picture re. #OBJEKT 6 and #OBJEKT 25 as they look different, what has to be updated doesn't appear to be between {}, correct? They're in your worksheet exactly as below? (leading <spaces> ???)
Updated version attached. Whatever leading <spaces> exist in In they're kept it Out
Records starting with #TRANS remain selected as expected