Text - Remove text Item No.99 (First 2 Chars) and move to end
 

I have series of nos in a spreadsheet listing.

Format is as folows:-

NNNNNNNN (8 Chars)

I need a way of searching where code is 99 - first 2 chars where exists and
be able to remove these from front of text string add to end of text string

Example Extract from Listing

Cell Reference V7

99030599 - Remove 99 and add to end, Result: 03059999
01020304 - No Change
99040405 - Remove 99 and add to end, Result: 04040599

Please advise as unsure which text functions needed to complete the
adjustment?

Many Thanks

Text - Remove text Item No.99 (First 2 Chars) and move to end
 

On Mon, 15 Jun 2009 15:10:01 -0700, dplunkett
wrote:

I have series of nos in a spreadsheet listing.

Format is as folows:-

NNNNNNNN (8 Chars)

I need a way of searching where code is 99 - first 2 chars where exists and
be able to remove these from front of text string add to end of text string

Example Extract from Listing

Cell Reference V7

99030599 - Remove 99 and add to end, Result: 03059999
01020304 - No Change
99040405 - Remove 99 and add to end, Result: 04040599

Please advise as unsure which text functions needed to complete the
adjustment?

Many Thanks




Perhaps:

=TEXT(IF(LEFT(TEXT(A1,"00000000"),2)="99",MID(TEXT (A1,"00000000"),3,6)&"99",A1),"00000000")

--ron

Text - Remove text Item No.99 (First 2 Chars) and move to end
 

Hi

With the value in A1 try this:

=IF(LEFT(A1,2)="99",VALUE(RIGHT(A1,6)&99),A1)


Regards,
Per

On 16 Jun., 00:10, dplunkett
wrote:
I have series of nos in a spreadsheet listing.

Format is as folows:-

NNNNNNNN *(8 Chars)

I need a way of searching where code is 99 - first 2 chars where exists and
be able to remove these from front of text string add to end of text string

Example Extract from Listing

Cell Reference V7

99030599 * *- Remove 99 and add to end, Result: 03059999 * *
01020304 * - *No Change
99040405 - * *Remove 99 and add to end, Result: 04040599

Please advise as unsure which text functions needed to complete the
adjustment?

Many Thanks

Text - Remove text Item No.99 (First 2 Chars) and move to end
 

If your strings are formatted as TEXT (to allow for leading 0s):

=IF(LEFT(A1,2)="99",MID(A1,3,6)&99,A1)

--
Biff
Microsoft Excel MVP


"dplunkett" wrote in message
...
I have series of nos in a spreadsheet listing.

Format is as folows:-

NNNNNNNN (8 Chars)

I need a way of searching where code is 99 - first 2 chars where exists
and
be able to remove these from front of text string add to end of text
string

Example Extract from Listing

Cell Reference V7

99030599 - Remove 99 and add to end, Result: 03059999
01020304 - No Change
99040405 - Remove 99 and add to end, Result: 04040599

Please advise as unsure which text functions needed to complete the
adjustment?

Many Thanks

Text - Remove text Item No.99 (First 2 Chars) and move to end
 

Many Thanks for your suggestions. The last thread posted was the best
solution for my query and has worked.

Thank you to all those that contributed to the post.

Regards

Dayle


"dplunkett" wrote:

I have series of nos in a spreadsheet listing.

Format is as folows:-

NNNNNNNN (8 Chars)

I need a way of searching where code is 99 - first 2 chars where exists and
be able to remove these from front of text string add to end of text string

Example Extract from Listing

Cell Reference V7

99030599 - Remove 99 and add to end, Result: 03059999
01020304 - No Change
99040405 - Remove 99 and add to end, Result: 04040599

Please advise as unsure which text functions needed to complete the
adjustment?

Many Thanks

Text - Remove text Item No.99 (First 2 Chars) and move to end
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"dplunkett" wrote in message
...
Many Thanks for your suggestions. The last thread posted was the best
solution for my query and has worked.

Thank you to all those that contributed to the post.

Regards

Dayle


"dplunkett" wrote:

I have series of nos in a spreadsheet listing.

Format is as folows:-

NNNNNNNN (8 Chars)

I need a way of searching where code is 99 - first 2 chars where exists
and
be able to remove these from front of text string add to end of text
string

Example Extract from Listing

Cell Reference V7

99030599 - Remove 99 and add to end, Result: 03059999
01020304 - No Change
99040405 - Remove 99 and add to end, Result: 04040599

Please advise as unsure which text functions needed to complete the
adjustment?

Many Thanks