Aligning text strings with master sequence

tabecker

New Member
Joined
Feb 26, 2021
Messages
3
Office Version
  1. 2011
Platform
  1. MacOS
Hi everyone,
I have text data that I need to line up with a sequence of letters in columns. I'm using Excel for Mac 2011, so it doesn't have the newest tools. I'm pretty sure that I need the functions IF, AND, MID, LEN and maybe INDIRECT. The problem is that I can't figure out what order to write the logic statements. I have uploaded a screen shot of an example. The text to be distributed is in A3. The desired result is highlighted C7:R7. The sequence to be matched is in Row 2, with corresponding numbers in Row 1. Column B has the starting number of the text that is to be aligned. Please note that this is small example. The sequence is actually over 500 letters long and the some of my text inserts are above 30 letters long.
I placed the corresponding formulas used (w/o the =) in cells A4:A6. I did succeed in using "AND" to achieve the empty space and "INDIRECT" to get a single letter in a cell, but not with putting it all together.
Thanks,
Tynan
 

Attachments

  • Screen Shot 2021-02-26 at 4.41.12 PM.png
    Screen Shot 2021-02-26 at 4.41.12 PM.png
    52.2 KB · Views: 2

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,935
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Hope I understand your description correctly.
I don't use a Mac, but this formula should work with it.
Formula in C7 dragged across to R7

Book3.xlsx
ABCDEFGHIJKLMNOPQR
13456789101112131415161718
2
3DTICIGYN8
4
5
6
7     DTICIGYN   
Sheet804
Cell Formulas
RangeFormula
C7:R7C7=IF(C1<$B3,"",MID($A3,COUNTA($B7:B7)-COUNTBLANK($B7:B7)+2,1))
 
Solution

tabecker

New Member
Joined
Feb 26, 2021
Messages
3
Office Version
  1. 2011
Platform
  1. MacOS
Hi,

Hope I understand your description correctly.
I don't use a Mac, but this formula should work with it.
Formula in C7 dragged across to R7


Thank you jtakw! It works perfectly. I'll have to look up and understand the COUNTA and COUNTBLANK functions. Thanks again!
 

tabecker

New Member
Joined
Feb 26, 2021
Messages
3
Office Version
  1. 2011
Platform
  1. MacOS
Thank you jtakw! It works perfectly. I'll have to look up and understand the COUNTA and COUNTBLANK functions. Thanks again!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,935
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top