How to subtract in multiple number patterns

[jeffmic]

Hi friends,

I have a series of number patterns in multiple cells that correspond to locations in time within the text below.
I need to shift all the numbers earlier based on one amount (i.e. deduct 559 from each 3-4-5 digit number pattern, but not from /0) and leave the rest of the data intact.

Thoughts? Ideas? Suggestions other than Excel?

This is a karaoke lyric scripting application that I need to shift a selection of lyrics earlier in the song, but only in a certain portion of the lyrics. I will copy the lyrics I need to shift into a separate file, change the number patterns and then paste it back into the full file.

Thank you for your help


-----------------------------
PAGEV2
C/A/1191/1881/0/0/0
Lit/ 1491/1580/0
tle / 1581/1664/0
sur/ 1665/1749/0
fer/ 1750/1831/0

C/A/1191/2159/0/0/0
Lit/ 1836/1923/0
tle / 1924/2004/0
one/ 2005/2109/0

C/A/1191/2472/0/0/0
Make / 2179/2252/0
my / 2253/2339/0
heart/ 2340/2422/0

C/A/1191/2832/0/0/0
come / 2423/2503/0
all / 2504/2587/0
un/ 2588/2675/0
done/ 2676/2782/0

-----------------------------
PAGEV2
C/A/0/0/0/0/0
/ 0/0/0

C/A/2553/3220/0/0/0
Do / 2853/2928/0
you / 2929/3014/0
love / 3015/3099/0
me/ 3100/3170/0

C/A/2553/3775/0/0/0
Do / 3185/3265/0
you / 3266/3347/0
sur/ 3348/3437/0
fer / 3438/3519/0
girl/ 3520/3725/0

C/A/2833/4169/0/0/0
Oo / 3784/3865/0
oo / 3866/4034/0
oo/ 4035/4119/0

-----------------------------
PAGEV2
C/A/0/0/0/0/0

[FlameRetired]

jeffmic welcome to the forum.

This is too vague. I don't see "shift" "earlier" (?) patterns you describe in the above example.

We are going to need two things:

• An uploaded Excel workbook (no pics or screenshots. It saves having to retype data.).
  Include a clearly marked BEFORE section (what you are starting with) and likewise an AFTER section.
• Include a detailed explanation of what you mean by 'shift' and 'earlier'. Point to (or refer to) specific cells and how they relate. Ie what the logic is, how you get from start values to end values. Please be sure to include more than one or two of these references.

If you are not familiar with how to upload a workbook:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Looking forward to your post.

[jeffmic]

I have attached a before and after example of what I'm requesting to solve.

thank you for your time,
Jeff

[FlameRetired]

jeffmic thanks for the upload. That' clearer.

Off hand the first formula solution that comes to mind uses 3 steps.

1. Determine how many sections of ?/?/? there are in each cell.
2. Parse the string and do the math inside the parsing formula. It's a long formula.
3. Then concatenate those results.

1. In the attached column C this formula counts the sections.

   Please Login or Register  to view this content.

2. In column D and filled across column K (or as far as needed to return all blanks) parses the string and does the math.

   Please Login or Register  to view this content.

3. In column L the concatenated results.

   Please Login or Register  to view this content.

I am exploring an alternative approach. It is half-baked at this point. I don't know if it will be any simpler or not.

Does this help.

[JohnTopley]

VBA function ...

Please Login or Register  to view this content.

in C4

=sub_n(A4,599)

Copy down

Highlighted value to subtract

[jeffmic]

Hi Flame - thank you!!

Most everything works except the lyric text to the far left MUST keep whether it has a space or not before the first '/'.
I see it's the last function in your 2nd piece of code, but I was unable to create an if statement to keep it or not. In other words, If the beginning of cell A5 shows You /, the space must remain there as it's the space between words in the lyrics. Cell A8 is the last word in that line, so it should have no space between down and / (down/).

[jeffmic]

Hi JohnTopley.

Thank you as well, but I've never created VBAs in Excel and I've been watching videos for the past hour trying to test your code. Would you be so kind to add your VBA to my file and attach it here as well? I've using Excel for Mac 2016 (15.34), but I'm sure a version by you will open fine.

[JohnTopley]

The code is in the file I posted and in the #5 thread.



Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]

[jeffmic]

Thank you John - I didn't notice the extension so I though it was my original file.
When I tried it, it didn't see the macro and only came up with NAME?

Ironically I tried to create my own again and it WORKED this time. I dunno

thank you so much, Jeff

[FlameRetired]

Hi Flame - thank you!!

Most everything works except the lyric text to the far left MUST keep whether it has a space or not before the first '/'.
I see it's the last function in your 2nd piece of code, but I was unable to create an if statement to keep it or not. In other words, If the beginning of cell A5 shows You /, the space must remain there as it's the space between words in the lyrics. Cell A8 is the last word in that line, so it should have no space between down and / (down/).

Sorry. I've been out visiting family.

I haven't looked at JohnTopley's macro yet.

I found another formula solution. It takes care of the above. It requires one helper formula. The end formula is not simple. It does the job.

The helper formula in column C finds the 1st string location to the left of the first number.

Please Login or Register  to view this content.

The final formula in column D.

Please Login or Register  to view this content.

[FlameRetired]

Another formula set. This takes four steps.

In column C returns the prefixes.

Please Login or Register  to view this content.

Column D returns the suffixes.

Please Login or Register  to view this content.

Column E substitutes the prefixes and suffixes out of the original string.

Please Login or Register  to view this content.

Column F subtracts -599 and re-appends the prefixes and suffixes.

Please Login or Register  to view this content.