[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

how can i remove a Z letter in a string

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
0
tenriquez39
Asked:
tenriquez39
4 Solutions
 
NBVCCommented:
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.
0
 
BenefordCommented:
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)
0
 
barry houdiniCommented:
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
0
 
Shanan212Commented:
If Z is at the end of the letter (always) then use this

=LEFT(C1,FIND("Z",C1)-1)
0
 
tenriquez39Author Commented:
thanks all
all was fine
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now