Concatenate mid, left then right data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rhaps - 10 Jan 2007 01:19 GMT

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

Reply to this Message

Roger Govier - 10 Jan 2007 01:43 GMT

Hi

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

Signature

Regards

Roger Govier

> Hi,
>
[quoted text clipped - 13 lines]
>
> Thank You

Reply to this Message

Ron Rosenfeld - 10 Jan 2007 01:49 GMT

>Hi,
>
[quoted text clipped - 13 lines]
>
>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

Reply to this Message

Rhaps - 15 Jan 2007 02:00 GMT

Hi,

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

Rhaps

Reply to this Message

Ron Rosenfeld - 15 Jan 2007 02:37 GMT

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

Thanks for the feedback

--ron

Reply to this Message