Abhilash1503

New Member
Joined
Sep 11, 2019
Messages
9
I have data in excel as mentioned below

Input
1a 1b 1c 4a 4b 4c 7a 7b 7c
2a 2b 2c 5a 5b 5c 8a 8b 8c
3a 3b 3c 6a 6b 6c 9a 9b 9c

Output
1a 1b 1c
2a 2b 2c
3a 3b 3c
4a 4b 4c
5a 5b 5c
6a 6b 6c
7a 7b 7c
8a 8b 8c
9a 9b 9c

As mentioned in the above i have data in input format,
and i need data as mentioned in output format.

Is there any formula for this........?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Abhilash1503,
Use the InStr function to find the starting position of the “a” , then use a combination of Left(), Right(), and Mid() functions to return the data

hth,
Computerman
 
Upvote 0
Is the input in 7 different columns and the output in 3?
 
Upvote 0
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
1a​
1b​
1c​
4a​
4b​
4c​
7a​
7b​
7c​
2​
2a​
2b​
2c​
5a​
5b​
5c​
8a​
8b​
8c​
3​
3a​
3b​
3c​
6a​
6b​
6c​
9a​
9b​
9c​
4​
5​
1a​
1b​
1c​
A5: =INDEX($A$1:$I$3, INT((ROWS($A$5:A5) + 2) / 3), MOD(3 * (ROWS($A$5:A5) - 1), 9) + COLUMNS($A$5:A5))
6​
4a​
4b​
4c​
7​
7a​
7b​
7c​
8​
2a​
2b​
2c​
9​
5a​
5b​
5c​
10​
8a​
8b​
8c​
11​
3a​
3b​
3c​
12​
6a​
6b​
6c​
13​
9a​
9b​
9c​
 
Upvote 0
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
1a​
1b​
1c​
4a​
4b​
4c​
7a​
7b​
7c​
2​
2a​
2b​
2c​
5a​
5b​
5c​
8a​
8b​
8c​
3​
3a​
3b​
3c​
6a​
6b​
6c​
9a​
9b​
9c​
4​
5​
1a​
1b​
1c​
A5: =INDEX($A$1:$I$3, INT((ROWS($A$5:A5) + 2) / 3), MOD(3 * (ROWS($A$5:A5) - 1), 9) + COLUMNS($A$5:A5))
6​
4a​
4b​
4c​
7​
7a​
7b​
7c​
8​
2a​
2b​
2c​
9​
5a​
5b​
5c​
10​
8a​
8b​
8c​
11​
3a​
3b​
3c​
12​
6a​
6b​
6c​
13​
9a​
9b​
9c​

<tbody>
</tbody>

Thanks for helping out but the output differs....

It's not in the format i requested....

It would be grateful if you help

Thanks in advance....
 
Upvote 0
Thanks for replying

Would you please workout a sample on that...

It will be great help for me...

Thanks in advance
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top