Seperate data based on condition

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hi,

I am trying to seperate data based on a condition.The data looks like below

COLA
BY TRANSFER-NEFT HDFC00010971097HF1106000112J PRAVEEN RANGAIAH--
BY TRANSFER-NEFT HDFC00012371237HF1105900016PADAPATI ASHOK--

I tend to get my data as below

COLA_____________________________________________________COLB_________-
BY TRANSFER-NEFT HDFC00010971097HF1106000112J___________PRAVEEN RANGAIAH--
BY TRANSFER-NEFT HDFC00012371237HF1105900016PADAPATI ___PADAPATI ASHOK--

The condition is traverse from the last character untill it encounters a number and to get the traversed string in seperate column

Thanks in advance for valuable solutions and suggestions

Thanks,
GD

2 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi GD,

Let me see if I understand you correctly:

You want to change:
BY TRANSFER-NEFT HDFC00010971097HF1106000112J PRAVEEN RANGAIAH
Into:
Column A: BY TRANSFER-NEFT HDFC00010971097HF1106000112J
Column B: PRAVEEN RANGAIAH

Then you say look from the right to left untill you encounter a number. If this is true then result should look like:
Column A: BY TRANSFER-NEFT HDFC00010971097HF1106000112
Column B: J PRAVEEN RANGAIAH

In your second example you show PADAPATI twice which isn't coherent with your first example.

I'm assuming you used underscores to create spacing. Try to use the <> symbol on the topright of your message, reading code, for better result.

Did I misunderstood you somewhere?

Best regards,
Trowa
Hi TrowaD.I made big blunder while typing.The data looks like below

COLA
BY TRANSFER-NEFT HDFC00010971097HF1106000112J PRAVEEN RANGAIAH--
BY TRANSFER-NEFT HDFC00012371237HF1105900016PADAPATI ASHOK--
I tend to get my data as below
COLA_____________________________________________________COLB______
BYTRANSFERNEFTHDFC00010971097HF1106000112___________JPRAVEEN RANGAIAH--
BY TRANSFER-NEFT HDFC00012371237HF1105900016 ___PADAPATI ASHOK

Thank You very much for looking into it

Thanks,
Dileep
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
HI Dileep,

Can't figure out how to search starting from the right.

But I did notice that the string before the name in both examples are the same. Hopefully that's also the case for the rest of your data.

To get the first string use this formula:
=LEFT(Sheet1!A2,44)
To get the names use this formula:
=MID(Sheet1!A2,45,100)

Does this work for you?

Best regards,
Trowa
Hi TrowaD,

Since I had many records, the string before the names is of varying lengths which is making it quiet tough to seperate.

Thank You
Dileep
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
That's a bummer Dileep.

Hopefully someone else will jump in to help out, since I don't have the answer for this one.

Good luck
Trowa