How to subtract in multiple number patterns

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 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.
   Formula:

   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.
   Formula:

   Please Login or Register  to view this content.

3. In column L the concatenated results.
   Formula:

   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.

VBA function ...

Please Login or Register  to view this content.

in C4

=sub_n(A4,599)

Copy down

Highlighted value to subtract

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

Originally Posted by 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/).

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.

Formula:

Please Login or Register  to view this content.

The final formula in column D.

Formula:

Please Login or Register  to view this content.

Another formula set. This takes four steps.

In column C returns the prefixes.

Formula:

Please Login or Register  to view this content.

Column D returns the suffixes.

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.