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

[dplunkett]

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

 

[Ron Rosenfeld]

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

 

[Per Jessen]

Hi

With the value in A1 try this:

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


Regards,
Per

 

[T. Valko]

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

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

 

[dplunkett]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!