Formula to Switch places

sakrams

New Member
Joined
Sep 28, 2009
Messages
47
Good Afternoon Excel Gurus,

Need help from the gurus with a quick formula to achieve the following result. I have a lot of sheets to fix and all sheets have 1000's of rows.

Column A has tags starting with numbers and some starting with characters. I am trying to write a formula that can convert the tags starting with characters to switch as shown in the table below. If the tag is starting with numbers remain with no change in a new column. Is there a quick way of achieving that?

AB
10002654-STL0002654-STL
2000322-ST000322-ST
30005-STM0005-STM
40012-STM0012-STM
50016-STM0016-STM
60019-STM0019-STM
70020-STL0020-STL
80020-STM0020-STM
90021-STL0021-STL
100021-STM0021-STM
110050-STL0050-STL
120075-STM0075-STM
130076-STM0076-STM
140078-STL0078-STL
15STL-00010001-STL
16STL-00050005-STL
17STL-00100010-STL
18STL-00110011-STL
19STL-00130013-STL
20STL-00140014-STL
21STL-00160016-STL
22STL-00170017-STL
23SM-00040004-SM
24S-00110011-S
25STM-00130013-STM
26STM-00150015-STM
27STM-00540054-STM
28STM-00550055-STM
29STM-00578900057890-STM
30STM-005877005877-STM
31SS-00010001-SS
32STS-00040004-STS
33STS-00060006-STS
34STS-00070007-STS
35STS-00080008-STS

<tbody>
</tbody>


https://i.postimg.cc/prD4w5dv/SS.jpg

Thanks a million in advance.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
164
Re: Help with a Formula to Switch places

Try this:

=IF(ISNUMBER(LEFT(A1)+0),A1,MID(A1&"-"&A1,FIND("-",A1)+1,LEN(A1)))
 

Forum statistics

Threads
1,089,615
Messages
5,409,324
Members
403,259
Latest member
Bubba49

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top