| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to truncate text characters
Greetings all,
This should be easy, but for the life of me I can't come up with the formula solution myself. I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. The manufactuer code is three digits, then followed by a "-", then comes the model number. I want to remove the first four text characters, the manufactuer code and the "-". Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. As in the past, I thank you for your anticipated help! Mark |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to truncate text characters
Try this:
=RIGHT(A1,LEN(A1)-4) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MarkT" wrote in message ... Greetings all, This should be easy, but for the life of me I can't come up with the formula solution myself. I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. The manufactuer code is three digits, then followed by a "-", then comes the model number. I want to remove the first four text characters, the manufactuer code and the "-". Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. As in the past, I thank you for your anticipated help! Mark |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to truncate text characters
Try
=MID(A1,FIND("-",A1)+1,99) If this post helps click Yes --------------- Jacob Skaria "MarkT" wrote: Greetings all, This should be easy, but for the life of me I can't come up with the formula solution myself. I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. The manufactuer code is three digits, then followed by a "-", then comes the model number. I want to remove the first four text characters, the manufactuer code and the "-". Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. As in the past, I thank you for your anticipated help! Mark |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to truncate text characters
Put this in a helper column:
=RIGHT(A1,LEN(A1)-4) then copy down as far as required. Hope this helps. Pete On Jun 4, 3:32*pm, MarkT wrote: Greetings all, This should be easy, but for the life of me I can't come up with the formula solution myself. *I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. *The manufactuer code is three digits, then followed by a "-", then comes the model number. *I want to remove the first four text characters, the manufactuer code and the "-". *Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. *As in the past, I thank you for your anticipated help! Mark |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to truncate text characters
That worked perfect! Thank you so much!
"MarkT" wrote: Greetings all, This should be easy, but for the life of me I can't come up with the formula solution myself. I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. The manufactuer code is three digits, then followed by a "-", then comes the model number. I want to remove the first four text characters, the manufactuer code and the "-". Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. As in the past, I thank you for your anticipated help! Mark |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to truncate text characters
This solution also worked! Thank you!
"RagDyeR" wrote: Try this: =RIGHT(A1,LEN(A1)-4) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MarkT" wrote in message ... Greetings all, This should be easy, but for the life of me I can't come up with the formula solution myself. I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. The manufactuer code is three digits, then followed by a "-", then comes the model number. I want to remove the first four text characters, the manufactuer code and the "-". Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. As in the past, I thank you for your anticipated help! Mark |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula to truncate text characters
You're welcome, and appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "MarkT" wrote in message ... This solution also worked! Thank you! "RagDyeR" wrote: Try this: =RIGHT(A1,LEN(A1)-4) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MarkT" wrote in message ... Greetings all, This should be easy, but for the life of me I can't come up with the formula solution myself. I would like to truncate some text characters of a model number which is currently made up of the manufactuer and model number. The manufactuer code is three digits, then followed by a "-", then comes the model number. I want to remove the first four text characters, the manufactuer code and the "-". Here is an example: CLI-67700002 CLI-67818003 CLI-67846003 OSP-13-37N20D OSP-13-37N30D OSP-13-37N9WA OSP-13-77N30D I would like to have the above replace with: 67700002 67818003 67846003 13-37N20D 13-37N30D 13-37N9WA 13-77N30D I am using 2007 w/ XP Pro. As in the past, I thank you for your anticipated help! Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Needed - Truncate extra spaces at end of variable text len | Excel Discussion (Misc queries) | |||
| truncate text to first word | Excel Discussion (Misc queries) | |||
| Trying to truncate or separate the first 3 characters/digits of co | Excel Discussion (Misc queries) | |||
| fill or truncate to a certain number of characters in a cell | Excel Worksheet Functions | |||
| linking cells in Excel 2003. How to not truncate to 255 characters. | Excel Discussion (Misc queries) | |||
Linear Mode
