| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Restrict no of chars in text box | Excel Discussion (Misc queries) | |||
| import text file with tab chars in it | Excel Discussion (Misc queries) | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
| remove blanks from a string of chars within a cell? | Excel Discussion (Misc queries) | |||
Linear Mode
