Concatenate mid, left then right data

[Rhaps]

Hi,

I have searched other messages and my brain must be on holiday because
I cannot get any of the answers I found to work for me.
What I have is a column of data which has to now be changed for GL
Reconciliation, looks like this-
SMITH J_000063MVINS
JOHNSTONE K_013565MVINS

And I now need it to look like this-
000063,J SMITH,MVINS

Most probably very simple but it still eludes me.....

Any help would be much appreciated.

Thank You

 

[Roger Govier]

Hi

Try
=MID(A1,FIND("_",A1)+1,6)&","&
MID(A1,FIND("_",A1)-1,1)&
LEFT(A1,(FIND("_",A1)-3))&
","&RIGHT(A1,5)

 

[Ron Rosenfeld]

Hi,

I have searched other messages and my brain must be on holiday because
I cannot get any of the answers I found to work for me.
What I have is a column of data which has to now be changed for GL
Reconciliation, looks like this-
SMITH J_000063MVINS
JOHNSTONE K_013565MVINS

And I now need it to look like this-
000063,J SMITH,MVINS

Most probably very simple but it still eludes me.....

Any help would be much appreciated.

Thank You

Here's one method:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this Regular Expression formula:

=REGEX.SUBSTITUTE(A1,"(\S+)\s(\w)_(\d+)","[3],[2] [1],")


--ron

 

[Rhaps]

Hi,

Thank you so much for the help, I have tried both and they work great.

Rhaps

 

[Ron Rosenfeld]

Hi,

Thank you so much for the help, I have tried both and they work great.

Rhaps

Thanks for the feedback

--ron