replace

Try this formula in B1 and copy down

Formula:

Please Login or Register  to view this content.

Excel 2016

	A	B
1	00123456789-FFF	00123456789
2	00123456788-FFF	00123456788
3	00123456787	00123456787
4	00123456777-FFF	00123456777
5	00123456999-FFF	00123456999
6	00123455555-FFF	00123455555
7	00123456444	00123456444

Sheet: Sheet3

Using your sample data...try this:

• Select the single-column range of cells to be impacted
• Data.Text-to-columns
...Click: Fixed width........Click: Next
...Insert a break point just before the dash...Click: Next
...Set the first column to TEXT
...Set the 2nd column to "Do not import column (skip)"
...Click: Finish

The end result will be:

	A
1	00123456789
2	00123456788
3	00123456787
4	00123456777
5	00123456999
6	00123455555
7	00123456444

Is that something you can work with?

try this on the cells having text =REPLACE(A1,LEN(A1)-4,5,"")

EDIT: I like Ron and Alkey's solution better than mine.

Since you indicated VBA....
When using VBA to implement Text-to-columns,
you have the option to indicate the data type of each field being parsed.

Sample data in A1:A7

Please Login or Register  to view this content.

Where:
Field 1 is text
Field 2 is anything, actually

Please Login or Register  to view this content.

The FieldInfo parameter, which is implemented this way, determines the data type:
FieldInfo:=Array(Array(0, 2), Array(11, 9))

For fixed-width source data,
the first argument of each array identifies a field by position (beginning with location 0)
and
the second argument of each array indicates the data type

Please Login or Register  to view this content.

OK...then, maybe this:

• Select the range of cells to be impacted
• Home...Number section...Select: Text
• Home.Find&Replace.Replace
...Find what: -FFF
...Replace with: (leave this blank)
...Click: Replace All

By converting the range to text first, the values won't convert to numbers after the "-FFF" is removed.

Does that help?

Here is with another formula

Formula:

Please Login or Register  to view this content.

Data Range

	A	B
3	9314-04321-77	9314-04321-77
4	7314-04736-88	7314-04736-88
5	1014-04920-99	1014-04920-99
6	00123456789-FFF	00123456789
7	00123456788-FFF	00123456788
8	123456787	123456787
9	00123456777-FFF	00123456777
10	1017777	1017777
11	1017888	1017888
12	1017777	1017777
13	00123456999-FFF	00123456999
14	00123455555-FFF	00123455555
15	123456444	123456444
16	1514-05156-11	1514-05156-11
17	1614-05158-44	1614-05158-44
18	00123456789-FFF	00123456789
19	00123456788-FFF	00123456788
20	123456787	123456787
21	00123433777-FFF	00123433777
22	1017755	1017755
23	1017800	1017800
24	1017777	1017777
25	00127756999-FFF	00127756999
26	00124445555-FFF	00124445555
27	00123456999-FFF	00123456999
28	00123455555-FFF	00123455555
29	123456444	123456444
30	1514-05156-11	1514-05156-11
31	1614-05158-44	1614-05158-44
32	00123456789-FFF	00123456789
33	00123456788-FFF	00123456788

Yes...Sorry...
Just formatting the range as Text doesn't quite complete the process.

Try this, instead

• Select the single-column range of cells to be impacted
• Home.Find&Replace.Replace
...Find what: -FFF
...Replace with: |FFF
...Click: Replace All

• Data.Text-to-columns
...Click: Delimited........Click: Next
...Other: | <-that's a pipe character (above the ENTER key)........Click: Next
...Set the first column to TEXT
...Set the 2nd column to "Do not import column (skip)"
...Click: Finish

Does that do what you want?

Thank you for the feedback!