Changing a string of data into another format.

**JC-Corona** · 02-05-2018, 12:35 PM

At work I receive a messy string of data in the wrong format for how I need it , was wondering if anyone could figure out a formula/formulas to change it.

I receive the data in the following format , where X represents a random letter and 1 is a random number.

XXX/11111.1.1

Just to make mattter worse sometimes the 3 letters at the front is only 2 letters , and the whole sequence is occasionally followed "/[Name]".

The format I need is 11111-0001-1.

For example "ATR/18691.2.7/Bell" , should be "18691-0002-7".

Im not even sure if this is possible however if it is can someone save me as it drives me insanse.

Cheers

**AlKey** · 02-05-2018, 12:43 PM

Try this
Enter in B1

Formula:

Please Login or Register  to view this content.

v	A	B
1	ATR/18691.2.7/Bell	18691-0002-7

**leelnich** · 02-05-2018, 02:21 PM

Hi all- here's a macro that extracts the number sections in column A using regEx and puts them column B.
Change the highlighted parts to reference your data column:

Please Login or Register  to view this content.

Changing a string of data into another format.

LinkBack

Thread Tools

Rate This Thread

Display

Changing a string of data into another format.

Re: Changing a string of data into another format.

Re: Changing a string of data into another format.

Thread Information

Users Browsing this Thread

Similar Threads

Formula that displays data from Changing String References

Applying custom data validation to string while changing string in custom function

Reading a string and changing data in a different cell

Changing date format in a text string

Changing data from one format into another

Changing format of data

[SOLVED] Changing data format

Bookmarks

Bookmarks

Posting Permissions