Extract 2 digits from a 3 digit number in different sequences

Hello Everyone,

I have a column with 3 digit numbers (A), then I have 3 columns where I pull each of the 3 digit number to show single digits in Columns (B, C, D)

Next in column "E" I pull the 1st and 2nd digit from the 3 digit numbers in column A and put them together.

Column F is my problem, I will explain after I explain Column G.

Column "G" I pull the 2nd and 3rd digit from the 3 digit numbers in Column A and put them together.

I have both E and G columns working with the formula Im using

Now for Column F i want to pull the 1st digit and the 3rd digit from the 3 digit number to put in column F together just like columns E and G.

I can't figure out how to pull the 1st digit and 3rd digit and leave 2nd digit alone for this Column F.

BTW, these numbers will continue over time and I will need to just drag down the column to update future 3 digit numbers


Thanks,
Brian

Try this. In F2 and fill down.

Formula:

Please Login or Register  to view this content.

Here's another way referencing the original number.

Formula:

Please Login or Register  to view this content.

Thanks FlameRetired and jtakw,

Both worked great.

I do have a question: Does it matter when it comes to slowing down excel calculations when you use the =B2&C2, =B2&D2, =C2&D2 for referencing?

The reason Im asking is the sheet of my example was only an example. My original will be referencing back to another sheet inside my workbook and then put my desired outcome in my new worksheet inside my workbook, and this workbook is pretty big, I was just wondering over time will doing it this way cause calculations to slow down the workbook and worksheets when calculating.

Also, I was wanting to take this one step further and do this if possible.

If for example through all my data in columns E, F, and G could I somehow be able to pinpoint certain numbers?

Example would be in column E the 15 shows twice, I would want to have a formula that would show the 15 and how often it was in column E. It could possibly be in a graph also that might help show my desired outcome.

I hope this makes since, If not. I will upload a more detailed excel sheet to show what Im talking about.

Thanks,
Brian

I think we need clarification. It looks like you've introduced another layer to this issue.

However, why not this for the middle column:

=LEFT(TEXT($A2,"000"),1)&RIGHT(TEXT($A2,"000"),1)

Yes, you’re correct Ali, I really didn’t think about the other until I put it in my excel sheet and then I seen I would probably need it through a graph or something similar to distinguish the Paired numbers. Do you want me to start a different thread on this?

Your formula worked great as well, was I correct about the =B2&C2 reference that i mentioned in the previous post about slowing down calculations?

Yes, I think so, as it is a completely different aspect requiring a different title.

Did you see my suggestion for your middle column? Like the others, I think you are probably overcomplicating this.

Originally Posted by Brian.Aerojet

I do have a question: Does it matter when it comes to slowing down excel calculations when you use the =B2&C2, =B2&D2, =C2&D2 for referencing?

The reason Im asking is the sheet of my example was only an example. My original will be referencing back to another sheet inside my workbook and then put my desired outcome in my new worksheet inside my workbook, and this workbook is pretty big, I was just wondering over time will doing it this way cause calculations to slow down the workbook and worksheets when calculating.

If for example through all my data in columns E, F, and G could I somehow be able to pinpoint certain numbers?

Example would be in column E the 15 shows twice, I would want to have a formula that would show the 15 and how often it was in column E. It could possibly be in a graph also that might help show my desired outcome.

Thanks,
Brian

Using =B2&C2, etc. is as simple as it gets, anything less, you'll have to type the numbers in by hand.
The original formulas you're using in E & G compared to this is Way more resource intensive with 3 Function calls.
So No, it shouldn't slow down calculation using this method, if calculations do get slowed down, there's something else amidst.

As for a "pinpoint" certain numbers, I'm not sure I understand what you mean, please elaborate.

And a formula to "show how often 15 is in Column E", Yes, but please explain what you mean, what dictates which Value to be Counted?

If One or more of the Values from Columns A thru G will be used in "graphing", then most likely you need the results as Real Numbers, as they are, it's Text.
I don't know where your Column A values are from, but to convert Column B thru G values to Real Numbers, just add +0 (that's plus sign zero) at the end of my suggested formulas for Column B, E, F, and G (Don't forget Column B formula is copied over to Column D, so Only need to change it for Column B).

If you like, I can make those changes and re-upload the file.

@AliGW

Yes, I did and also yes I'm real bad for overcomplicating a lot of things, lol

Thanks

Thanks jtakw, Im really not near what you guys are when it comes to the different uses of formulas that do exact same thing. I would much rather use your suggestion as it is much easier. The reason I first asked about it after your post is because I was under the impression that the more you use a formula like the =B2&D2 as reference to another sheet it would cause issues with the calculations. Like I said though, Im not near what you guys are, so i take what you guys tell me and that is good with me.
Also, Im going to do as Ali said and start a new thread regarding the pinpoint of paired numbers in columns E, F, and G. Im hoping to get it posted in next little while, I want to make sure I explain it to where you guys will understand what i want as my outcome.

Originally Posted by jtakw

@AliGW

Isn't your suggestion in Post #6 more complicated than =B2&D2 as I suggested in Post #4?

Yes, but it does not need the helper columns B and D.

It really depends on what you are trying to achieve.

They are Not helper columns, not that I understand anyways, they're In OP's original file and he never mentioned he was using them as Helpers.

The comment was in response to Brian's query, not aimed at you or the solutions you have offered.

What is not clear to me is whether B, C and D are required, or whether they are helper columns. That's why I said this in post #14:

It really depends on what you are trying to achieve.

You'd be surprised how often people fail to mention details like this, assuming it's obvious. Our different interpretations show how important it is to be specific.

Ok, no, they’re not helper columns. I’m doing paired number test. For example if the 3digit number is 628 then the paired numbers are 62, 68, and 28, which is 1st and 2nd number paired (62), then 1st and 3rd number paired (68) and also 2nd and 3rd number paired (28)

@jtakw
I will have to show 04 and not 4, I’m not sure how this will happen according to what you’re telling me that it needs to be converted to real numbers, but that would change the 04 to 4.
Would a helper column take care of this issue to be able to keep it showing as 04?

OK, in that case, ignore my suggestion - it's not necessary. Just concatenate as was advised earlier:

=TEXT(B2,"000")&TEXT(D2,"000")

Sounds good, using the post #4 suggestion.