how can i remove a Z letter in a string

tenriquez39
tenriquez39 used Ask the Experts™
on
hi

someone know a function to remove letter Z from 3th clolumn
so , by example, string NF114Z must change to NF114,    NF102Z must change to NF102

01NAEB      NC31      NF114Z            SYSGERMX02      19/12/2012      111400
13IMBC      NC31      NF102Z            SYSGERMX02      19/12/2012      111400
20NAGB      NC31      NF90Z            SYSGERMX02      19/12/2012      111400
21NACA      NC31      NF100Z            SYSGERMX02      19/12/2012      111400
23NADA      NC31      NF116Z            SYSGERMX02      19/12/2012      111400
23NAEB      NC31      NF115Z            SYSGERMX02      19/12/2012      111400
27NAEN      NC31      NF82Z            SYSGERMX02      19/12/2012      111400
29NACA      NC31      NF92Z            SYSGERMX02      19/12/2012      111400
29NAFB      NC31      NF93Z            SYSGERMX02      19/12/2012      111400
31IMBL      NC31      NF103Z            SYSGERMX02      19/12/2012      111400
44NABD      NC31      NF95Z            SYSGERMX02      19/12/2012      111400

thanks a lot
EXPORTNC31.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
NBVC
Commented:
If that is the only Z in the column, you can select the column, the hold CTRL key, and hit H key (CTRL+H) , then replace Z with nothing in the replace with box.

Click Replace ALL.

if there are other possible Z's, then use a formula in another column like:

=LEFT(C2,LEN(C2)-1)  copied down.

Then you can copy this column and go to column C and paste Special > Values over the original.  Then delete the formula column.
Beneford
Commented:
Since the 'Z' is always there and is alway the final character, you can use the following formula in column D: (put this in D1 and copy down the column).

=LEFT(C1,LEN(C1)-1)

If the 'Z' is not always at the end, you can remove the first Z with this formula:

=LEFT(C1,FIND("Z",C1)-1)&MID(C1,FIND("Z",C1)+1,9999)
barry houdini
Most Valuable Expert 2013
Commented:
You could simply use Edit > Replace for a range of cells, replace "Z" with nothing.....

or you could use a formula in another column, e.g. with data in C2 put this formula in an empty cell in row 2 and copy down

=SUBSTITUTE(C2,"Z","")

regards, barry
Commented:
If Z is at the end of the letter (always) then use this

=LEFT(C1,FIND("Z",C1)-1)
tenriquez39Engenier

Author

Commented:
thanks all
all was fine

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial